DB Gate User Manual - Raz-Lee
Contents
1. Work with Directory Entries To view a specific subset of drivers enter the first alphanumeric characters of the driver in the Subset by driver field and press Enter The driver list is redisplayed with results that match your query B Gate User Manual Version 1 5 Chapter 2 Remote DB Definitions 9 Modifying a Directory Entry You can update the details of a Directory Entry by highlighting it and choosing 1 Select The Modify Directory Entry screen appears with the selected directory entry filled in Once you modify the directory entry press Enter then press Enter again to confirm your changes NOTE You are not able to update the Directory Entry Name or Driver Modify Directory Entry Directory Entry LOGISTICS Active Y Driver MSSQL2005 Description This is a MS SQL LOG ee ee Eee es Host or IP lt host gt Port es ss ss lt port gt Catalog lt catalog gt Schema lt schema gt Database lt db gt AdventureWorks O DFt 1 None 2 Basic 4 All URL Jdbe sqlserver lt host gt lt port gt databaseName lt db gt Auto replacments are lt host gt lt port gt lt catal og gt lt schema gt lt db gt lt adI 1 4 gt Language Support 4 xAUTO NONE 4 11 for Bidi F3 Exit F4 Prompt F7 Driver page F8 Replace driver F1i2 Cancel Modify Directory Entry Screen DB Gate User Manual Version 1 5 Chapter 2 Remote DB Definitions 12. A help window appears containing explanatory text that relates to the function or option currently in use Online help will shortly be available in Windows help format for viewing on a PC with terminal emulation Typography Conventions E Menu options field names and function key names are written in Bold E References to chapters or sections are written in talic E OS 400 commands and system messages are written in Bold Italic E Key combinations are separated by a dash for example Shift Tab E Emphasis is written in Times New Roman bold DB Gate User Manual Version 1 5 About This Manual 1 Other iSecurity Products Raz Lee s iSecurity is an integrated state of the art security solution for all System 1 servers providing cutting edge tools for managing all aspects of network access data and audit security Its individual components work together transparently providing comprehensive out of the box security Other iSecurity products include DB Gate User Manual Version 1 5 About This Manual Change Tracker Change Tracker automatically tracks modifications in the software and file structure within production libraries Changes are tracked at both the object and source levels It does not require any special actions by programmers COMMAND COMMAND monitors and filters commands and its parameters before they are run enabling you to control each parameter qualifier or element in conjun
3. Driver file Path to the jar file on the IFS that contains the JDBC driver for this database Class JDBC driver class name URL for the driver developer e webpage Default port The default port to use when setting up a connection Additional You may define up to four parameters which are specific Parameters for this driver The definition includes the label as well as a short explanation or the possible values Parameters which are defined here will be displayed when this driver is selected DB Gate User Manual Version 1 5 Chapter 3 DB Drivers Additional Commands When in the Driver field displays a Select DB Driver window with the option to select a specific DB Driver After entering 1 Select the window closes and your selection is displayed in the Driver field F7 Once the Driver page has been entered selecting F7 opens the URL in your default browser Restores the original definition of a driver if it was supplied alongside the DB Gate and replaces the current settings Copying a DB Driver To copy a DB driver do the following 1 Select the DB driver you want to copy and press 1 Copy then press Enter The Copy DB Driver screen opens with the name and description of the selected driver In the new Driver field change the name then press Enter 3 After the confirmation message appears asking if you want to modify any of the entries press Enter again to save the new driver Copy DB Driver Type choice
4. AS 400 and often include hidden infrastructure costs DB Gate User Manual Version 1 5 Chapter 1 Introducing DB Gate 5 DB Gate Benefits E Opens the entire non DB2 spectrum of databases E Greatly expands programmer s capabilities when working with non DB2 databases E Reduces the need for redundant data and ETL extract transformation load data manipulation products E Eliminates the need for SQLPKG even when accessing another DB2 database including one on the AS 400 E Unique technology enables transparent access to any database MySQL ORACLE MS SQL DB2 Informix SQLite or data source e g Excel which resides on any IBM or non IBM platform using STRSQL STRQM or programs in languages such as RPG Cobol etc compiled using CRTSQL E Integration with the STRSQL lets you prompt to see the Table names Column names and more E Expands IBM i based DRDA functionality by enabling transparent connectivity with JDBC databases not supported by DRDA E Uses standard SQL syntax and is based upon standard OS 400 functionality so there is virtually no learning curve to get up and running E Remote Server Authentication that makes use of the IBM Server Authentication Entries injecting them seamlessly when needed and eliminating the need to remember and re enter a user name and password for each CONNECT to a remote DB E Provides detailed traceability logs DB Gate Restrictions The following functions are not su
5. Driver for Excel CSV ODS and Text Files sc0ss000 40 Adding an Entry ccccccceccceecceeceseeeseeeceeeeeeeeaeeeseeeseeeeeueeseeeseeesaeeseueesueeseeesaeesueseeeseeeseeeseeeganes 40 Working with the JDBC Driver 41 DB Gate User Manual Version 1 5 Table of Contents About This Manual This user guide is intended for system administrators and security administrators responsible for the implementation and management of security on AS400 systems However any user with basic knowledge of AS400 operations will be able to make full use of this product after reading this book Product Documentation Overview Raz Lee takes customer satisfaction seriously Our products are designed for ease of use by personnel at all skill levels especially those with minimal AS400 experience The documentation package includes a variety of materials to get you familiar with this software quickly and effectively Printed Materials This user guide is the only printed documentation necessary for understanding this product It is available in user friendly PDF format and may be displayed or printed using Adobe Acrobat Reader version 4 0 or higher Acrobat Reader is included on the product CD ROM This manual contains concise explanations of the various product features as well as step by step instructions for using and configuring the product Online Help AS400 context sensitive help is available at any time by pressing the F1 key
6. SQLERR 204 You have an error in your SQL syntax check t WAREHOUSE PREPARE SQLERR 104 SQL0104 Token FROM1 was not valid Valid gt CRM PREPARE SQLERR 204 You have an error in your SQL syntax check t Bottom F3 Exit FiQ Entire Entry Fi7 Top F18 Bottom To view further details on the output select F10 Entire Entry The following screen appears Display DB Gate Log Additional Entry Information System 520 Remote DB Entry WAREHOUSE From User Profile DB Date sent 12 03 22 Time sent 10 23 47 From Job QZRCSRVS QUSER 215534 Program DBCRTLGR Remote User DB Library SMZB Remote IP SQL Opr PREPARE SQL State 42704 SQL Err 204 SQL Error Message SQL0204 DEMOPF21 in ILAN type xFILE not found SQL Statement select x from ilan demopf21 F3 Exit Fi2 Cancel Additional Entry Information from DB Gate Log DB Gate User Manual Version 1 5 Chapter 4 DB Gate Log 22 Chapter 5 Remote User ID This section details how to work with server authentications The parameters and settings rely on standard IBM commands such as ADDS VRAUTE and CHGS VRAUTE which are described in detail in IBM s literature At the time that authentication information is required the data entered here will be used This eliminates the need for repetitive entries of the authentication information Injection of Remote User IDs To set up auto injection of remote us
7. is the same as the Modify Directory Entry Screen above Add New Directory Entry Directory Entry Driver F3 Exit F4 Prompt Fi2 Cancel Add New Directory Entry DB Gate User Manual Version 1 5 Chapter 2 Remote DB Definitions 12 Chapter 3 DB Drivers Working with Database Drivers A database driver enables you to access a remote database first by recognizing it and then by establishing a connection with it NOTE DB Gate comes with pre installed DB drivers You may add any number of additional drivers or modify the existing ones To set up a database driver select 11 Work with DB Drivers from the main menu The following Work with DB Drivers screen with a three column list of the different types of databases already entered in the system and from which you can choose to create a remote connection It includes the following details E Driver Name E Available Yes indicated that the driver s Driver file parameter in the Modify DB Driver screen has been filled in with the path to the jar file on the IFS E Description for the driver Work with DB Drivers Subset by driver Type options press Enter by text 1 Select 3 Copy 4 Delete 8 Driver page Opt Driver Available AS400DB2 Yes DB2 on OS 400 platform CACHE Cache DAFFODILLO S DaffodilDB Local DAFFODILSR DaffodilDB Server DB2 Yes DB2 for Windows Linux DB2JDBC4 Yes DB2 for Windows Linux DERBY Yes JavaDB Derby FIREBIRD Yes Firebird FRONTBASE Yes FrontB
8. press Enter After the confirmation message appears press Enter again to save your settings Modify User Authentication Entry Type choices press Enter User profile Server Remote user ID SASHAZ Password or XNONE DB Gate restricts Server to 18 characters User and Password are restricted to 10 characters PS ExXit F12 Cancel Modify User Authentication Entry Screen Adding a New Server Authentication Entry To add a new server authentication entry press F6 Add New An empty Add User Authentication Entry screen appears DB Gate User Manual Version 1 5 24 Chapter 6 Test Drive This section includes the various ways you can start SQL and work with SQL programs These features rely on standard IBM commands which are described in detail in IBM literature Starting SQL To begin writing SQL commands directly from your AS 400 select 51 Start SQL from the main menu and press Enter The following Enter SQL Statements screen is displayed Enter SQL Statements SQL statement press Enter CONNECT TO SALES Current connection is to relational database SALES SELECT FIRST NAME LAST NAME EMAIL PHONE NUMBER FROM HR EMPLOYEES WHERE FIRST NAME BETWEEN Jack AND Oliver SELECT statement run complete Bottom F3 Exit F4 Prompt F6 Insert line F9 Retrieve F10 Copy line Fi2 Cancel Fi3 Services F24 More keys Enter SQL Statements Screen The Enter SQL Statements display is the main screen f
9. the menus so you are never required to memorize arcane commands Many features are also accessible via the command line for the convenience of experienced users Menus Product menus allow for easy access to all features with a minimum of keystrokes Menu option numbering and terminology are consistent throughout this product as well as other Raz Lee products To select a menu option simply type the option number and press Enter The command line is available from nearly all product menus If the command line does not appear and your user profile allows use of the command line press F10 to display it Data Entry Screens Data entry screens include many convenient features such as E Pop up selection windows E Convenient option prompts E Easy to read descriptions and explanatory text for all parameters and options DB Gate User Manual Version 1 5 Chapter 1 Introducing DB Gate 7 E Search and filtering with generic text support The following describes the different data entry screens E To enter data in a field type the desired text and then press Enter or Field Exit E To move from one field to another without changing the contents press Tab E To view options for a data field together with an explanation press F4 E To accept the data displayed on the screen and continue press Enter The following function keys may appear on data entry screens Function Key Description Display context sensitive help End the current
10. whenever the retention period expires The STD program is SMZB DBSOURCE DBDBGBKP F3 Exit Fi2 Cancel DB Log Retention Screen DB Gate User Manual Version 1 5 Chapter 7 System Configuration 29 Parameter Description Data retention period The number of days that the queries are retained At the end of this period queries are purged from the log Enter 99 to retain all data indefinitely Backup Program for data Enter the name of the backup program you wish to use type STD to use the standard backup program or NONE for no backup Enter the name of the library where it is located DB Gate User Manual Version 1 5 Chapter 7 System Configuration 30 Chapter 8 Maintenance Menu The Maintenance Menu lets you set and display global definitions for DB Gate To open it select 82 Maintenance Menu from the main menu DBMINTM Maintenance Menu iSecurity DB Gate System 520 Select one of the following xPatent Pendingx Definitions General 5 Dispaly DB Gate Definitions 532 Check Locks 53 Add DB Gate to Java CLASSPATH Operators Journal Definition Files 11 Work with Operators 71 Add Journal 72 Remove Journal 79 Display Journal Uninstal 91 Uninstal Selection or command F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Information Assistant F16 AS 400 main menu Maintenance Menu Screen Display DB Gate Definitions To print the definitions that you entered for DB Gate do the followin
11. 0 The following table describes the detailed information you enter to create a new DB driver Some of the parameters may vary depending on the selected driver Parameter Description Log 0 Global default this value is taken from System Configuration s General Definitions screen 1 No log no data is stored 2 Connect the log will store a record of each connection and disconnection from a database 4 All the log will store all commands sent when a Fetch command is sent it will only store the first in the series The address used to access the remote database The port number associated with the above address required for setting up a connection to the remote database Catalog If the database requires a Catalog for the connection specify it here Schema The schema for the remote database Additional parameters Up to four different parameters based on the selected SID in this example driver URL Provided by the DB driver provider May include parameters lt host gt lt ip gt lt user gt lt pwd gt lt catalog gt lt port gt lt schema gt lt adl1 4 gt AUTO Default setting NONE 4 11 Based on an IBM setting for specific language support For more details see Replace Driver Enables users to replace the driver configuration for this RDB entry Language Support DB Gate User Manual Version 1 5 Chapter 2 Remote DB Definitions 11 Copying an Existing Directory Entry You can choose to add a ne
12. DB Gate The Remote Database Access Component of iSecurity iSecurity User Manual Version 1 5 RAZ LEE gt The iSeries Security Experts Release November 25 2012 Copyright Notice Copyright Raz Lee Security Inc All rights reserved This document is provided by Raz Lee Security for information purposes only Raz Lee Security is a registered trademark of Raz Lee Security Inc Action System Control User Management Assessment Firewall Screen Password Audit Capture View Visualizer FileScope Anti Virus AP Journal are trademarks of Raz Lee Security Inc Other brand and product names are trademarks or registered trademarks of the respective holders Microsoft Windows is a registered trademark of the Microsoft Corporation Adobe Acrobat is a registered trademark of Adobe Systems Incorporated Information in this document is subject to change without any prior notice The software described in this document is provided under Raz Lee s license agreement This document may be used only in accordance with the terms of the license agreement The software may be used only with accordance with the license agreement purchased by the user No part of this document may be reproduced or retransmitted in any form or by any means whether electronically or mechanically including but not limited to photocopying recording or information recording and retrieval systems without written permission given by Raz Le
13. Dmver 17 Deleting a DB Driver 17 Drivers amp LICENSES Folders ccccccseccceeeceeeceeeccececeeecueceueceueeceeeceuesueesueeseeeseeeseeesseeeseeeneeess 18 Chapter 4 DB Gate LOg E 19 Chapter 5 Remote User Meester nnnm nnmnnn 23 Injection of Remote User Us 23 Modifying a Server Authentication Entry c cccccceccceccceeeeeeeeeeeeeeeeeeesseeeeaeeeeseeeeseesaaees 24 Adding a New Server Authentication Entry cccccccccseccceeceeeeeeseeeeseeeeseeeeseueeseeeesaeeesaes 24 Chapter 6 TeSt DIVE tinnidenitertiieeraasaceaisutanaisdnidsnnaatencnisin 25 Sg die E E 25 Working with SQL Program Gources 26 DB Gate User Manual Version 1 5 Table of Contents Chapter 7 System Configuration sek SERREENRRE ERR EENRRE ERR EEN RRE RE EEN RREEN REENEN Een 27 ELE Re Te a eee eee ae en E eo oe an oe ee ee 28 OG p CLS el 29 Setting DB Log Retention Parameters n nnnnnnennnennunnsnnnrnnnnnnrnrrnrrnnrrnnrrnrrnnrrsnrrenrnenen 29 Chapter 8 Maintenance Menu cccsccssssecsesseccesscessecenseeensseeeasesensseneaseseasessens 31 Display DB Gate UDetmtons 31 Work with OC e 33 Eege 35 Add DB Gate to Java CLASSPATH sssccscccscscseacsanasce0sen04n05e005en0sdn05da9send2ensheedvacdtanshacduacdeseseacie 36 Journal Definition GI 37 PAGO NNO ee OUTING erenn rE amet anneaenasesbsentsnee somnsonnasensenescensnsesssten 37 FIC MOVING VOUA E 38 Displaying AS OUTING sssrin a ARN A aaa Aa ENa 39 AS EE 39 Appendix A JDBC
14. LL SQL Operation a a a a a a ALL SQL State we e amp A 2 e A XALL SQL Error Code e XALL SQL Statement contains XALL F3 Exit F4 Prompt F5 Refresh Fi2 Cancel F24 More keys Number xXBYTIME Date xCURRENT XSTART Time Date xCURRENT xXYESTERDAY Time Name generick xALL Name genericx XALL Name genericx xALL Name XALL ALTER CLOSE Character genericx KLL Number XERR XNOERR xALL More F13 How to use this display Display DB Gate Log Entries Screen B Gate User Manual Version 1 5 Chapter 4 DB Gate Log 19 Display DB Gate Log Entries Type choices press Enter SQL Message contains From job name oe se we se ee aS Name genericx xALL User jee e ms amp eg e amp XALL Name genericx ALL Number 2 2 ee ee SLL 000000 999999 xALL From program name XALL Name genericx XxALL Library e er Ae e XALL Name genericx XxALL Number of records to process NOMAX Number xNOMAX Output 2 e ee e sp r eg e S x x SPRINT XOUTFILE Bottom F3 Exit F4 Prompt F5 Refresh Fi2 Cancel Fi3 How to use this display F24 More keys Display DB Gate Log Entries Screen Cont The following table describes the various settings you can define to filter log entries Parameter Description Display last minutes Number BYTIME This Backward Glance feature lets you enter the number of minutes of recent activi
15. a Journal To add a journal select option 71 Add Journal from the Maintenance Menu screen The following Create Journal Confirmation screen appears Press Enter to confirm DBMINTM Maintenance Menu iSecurity DB Gate System 520 Select Pendingx Create Journal Confirmation Definit 3 Dis You are about to start journaling the product files The journal receivers will be created in library SMZBJRND If this library does not exist it will be automatically created Operato 11 Wor If you wish to create the library in a specific ASP you should press F3 Exit create this library and run again this option Press Enter to start journaling F3 to Exit F3 Exit Selecti ST 7i F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Information Assistant F16 AS 400 main menu Create Journal Confirmation Screen DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu Removing Journal To remove a journal select option 72 Remove Journal from the Maintenance Menu screen The following End Journal Confirmation screen appears Press Enter to confirm DBMINTM Maintenance Menu Select End Journal Confirmation Definit 3 Dis You are about to end journaling the product files The journaling will stop in library SMZBJRND Press Enter to end journal ing Operato 11 Wor Uninstall 91 Uninstall Selection or command gt 72 F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Informa
16. ase HSQLDB Yes HSQLDB HSQLDBEMBD Yes HSQLDB Embedded H2 EMBED Yes H2 Database Engine Embedded H2 MEM Yes H2 Database Engine In memory mode H2 SERVER Yes H2 Database Engine Server mode F3 Exit F6 Add new F8 Pr int Fi2 Cancel Work with DB Drivers Screen B Gate User Manual Version 1 5 Chapter 3 DB Drivers 13 The following driver configurations are currently supplied alongside the product E Cache E MS JDBC E Daffodil E MS SQL 2005 E DB2 for Windows Linux E MySQL E JavaDB Derby E Oracle E Firebird E Pervasive E Frontbase E PointBase E HSQLDB E PostgreSQL E Informix E Sybase SQL Anywhere E Mimer E Sglite E MS Excel E Sybase ASE If the URL for the driver manufacturer has been saved within the driver s details you can open the driver s webpage in your browser and read detailed information on the database To do this select 8 Driver page and then press Enter To view a specific subset of drivers do one of the following E In the Subset by driver field at the top right of the screen enter the first alphanumeric characters of the driver and press Enter The driver list is redisplayed with results that match your query E In the Subset by text field enter any alphanumeric characters you want to match in either the Driver Name or Description fields and press Enter The driver list is redisplayed with results that match your query Modifying a DB Driver To modify a DB driver do the following 1 Highli
17. ction with the context in which it is about to run Options include Allow Allow with Changes and Reject It includes a comprehensive log proactive alerting and easily integrates with SIEM AOD Authority on Demand provides an advanced solution for emergency access to critical application data and processes which is one of the most common security slips in System 1 AS 400 audits Current manual approaches to such situations are not only error prone but do not comply with regulations and often stringent auditor security requirements Capture Capture silently captures and documents user screens for tracking and monitoring without any effects on system performance It also preserves job logs for subsequent review Capture can run in playback mode and can be used to search within texts AP Journal AP Journal automatically manages database changes by documenting and reporting exceptions made to the database journal Anti Virus Anti Virus is a dedicated iSeries specific product engineered to provide full protection to the server its file contents and resident iSeries or System 1 dedicated software Visualizer Visualizer is an advanced DWH statistical tool with state of the art technology This solution provides security related data analysis in GUI and operates on summarized files hence it gives immediate answers regardless of the amount of security data being accumulated Firewall Firewall protects and secures all types of acce
18. e Security Inc Visit our website at http www razlee com Record your Product Authorization Code Here Computer Model Serial Number Authorization Code Table of Contents POUR Teen 1 Product DOCUMENTATION Cwvenlew a 1 FGI OIA e E Gace Gace dacetudedaceescesacedacedccat 1 OTANI ST EE 1 Typo py C ONEN e 1 Other iSecurity PDroduce cece eeseeeesseeeeseeesseeessaeeseaeesseessneetseeesseeesaaees 2 Chapter 1 Introducing DB Gate cccsccccesseesseeeeeeeeceseeeenseeeeseeeeneeseeseesenseseeneesoaees 4 SYSTEM Heouemente ssrtsesss a a RER aa RER a a 4 Sana DE GAE ars etserereearetpaceesaceetaceetaaeniacetai E E E AT OE OEE 4 lu You Need WI E 5 Typical Remote Access Limitations ccccccceeccecceeeeeeseeeeeeceeeseeeeueeeeeneeeaueseeeneeeaueneeenaes 5 DB ale BENGIS sescca esas tase asec eenieden en een nweacdleneteaaaes 6 DB Gate ROSSI CUO INS secsi E E eee eS 6 Native OS 400 Text Based User lntertace 7 lie 7 ACHT iG New Features and Funchonaltv 8 Ve 0 WE 8 Chapter 2 Remote DB Definitions cccccceseceeeseeceseeeeeseeeeneesenseseeseesensenenseesenees 9 MV ONG ak IFSC L ORY nl 9 Modifying a Directory Emir 10 Copying an Existing Directory Eniya A 12 Creating a New Directory Entry EE 12 Chapter 3 DB EVO Se aa treason sangre cr asics napasneen pacieenipedeeeasscasirageers pass anetacianeens 13 Working with Database Drivers 13 Modifying a DB Ri 14 Copying a DB Driver 16 Adding a New DB
19. er IDs select 21 Work with Server Authentication from the main menu and press Enter The following Work with Server Authentication Entries screen is displayed Work with Server Authentication Entries Subset by user Type options press Enter server 1 Select 4 Remove remote user Opt User Server Remote user Password AU CRM1 SASHA2 AU RDB IMP QSECOFR YES AU S520DDM QSECOFR YES AV testrdb10 AV DB CRM SASHA YES DB LOGISTICS sasha YES DB SALES tml YVES DB2ADMIN acum db DB2ADMIN xXYES DB2ADMIN ACUMSDB db2admin YES DPROP ODED DPROP DPROP REPLICA ODED DPROP SHI DPROP DPROP 192 168 168 182 DPROP DPROP 192 168 168 4 DPROP P3 Exit F6 Add New Fi2 Cancel Work with Server Authentication Entries Screen To view a specific subset of server authentication entries select one of the following subset fields User Server or Remote User Enter the first alphanumeric characters of the entry and press Enter The list is redisplayed with results that match your query B Gate User Manual Version 1 5 Chapter 5 Remote User ID 23 Modifying a Server Authentication Entry To modify a server authentication entry do the following 1 Highlight the server authentication entry that you want to update in the Work with Server Authentication Entries screen and press 1 Select then press Enter 2 When the Modify User Authentication Entry screen opens update the User profile Server and Remote user ID details displayed 3 When you are done
20. es ODF spreadsheet LibreOffice OpenOffice and text files Adding an Entry 1 To add an entry type STRDB then select option 1 Work with Directory Entries Press F6 give it a name and point it to the supplied PC_FILE driver Press Enter Set log level and language support if needed and confirm The entry is activated and available for use Add New Directory Entry Directory Entry PC Driver a 2 ew we PC_FILE F4 Prompt The supplied driver in option 11 no editing is needed here Opt Driver Available ORACLE_SID Yes Oracle SID ORACLE_SRV Yes Oracle Service 7 PERVASIVE Pervasive POINTBASEL Yes PointBase Local POINTBASES Yes PointBase Server B Gate User Manual Version 1 5 Appendix A JDBC Driver for Excel CSV ODS and Text Files 40 The driver operates based on target file s extension E xls for MS Excel E csv for comma separated values E ods for ODF spreadsheet E txt and any other extension is treated as text file Working with the JDBC Driver 4 Connect the entry no user password is required CONNECT TO PC 5 Complete the following steps A An SQL COMMENT ONT statement COMMENT ON QGPL MY_TABLE IS file tmp customers xls One can also use COMMENT ON QTEMP MY_TABLE IS or COMMENT ON MY_TABLE IS In both cases the file MY_TABLE is created in an internal QTEMP library This is not to be confused with the current Job s QTEMP lib Also
21. g 4 From the Maintenance Menu screen select 5 Display DB Gate Definitions The following screen appears 5 When you are done press Enter After the confirmation message appears press Enter again to save your settings B Gate User Manual Version 1 5 Chapter 8 Maintenance Menu 31 Display DB Gate Definitions Type choices press Enter Report type From item To item Format Output gt XDBENTRY xXALL XDBENTRY xDBDRVR BALL Character value XALL xSTART xSAME Character value XONLY XxLAST xXDETAILS XLIST xDETAILS xX xX XPRINT Bottom F3 Exit F4 Prompt F5 Refresh F12 Cancel Fi3 How to use this display F24 More keys Display DB Gate Definitions Screen Parameter Report Type Description ALL All of the following options DBENTRY The definitions for the Directory Entries DBDRVR The definitions for the DB Drivers Character value Al Start From the beginning of all values Character value Only Last List Short form Details Full form Screen Print Spool DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu 32 Work with Operators The operators authorities management is maintained in one place for this product It also offers the site a possibility of implementing a second password to protect use of the product There is one default group AUD SECAD It allows all users both AUDIT and SECADM special authorities By default this g
22. ght the DB driver you want to update and press 1 Select then press Enter The Modify DB Driver screen opens with the driver s details 2 Update the fields then press Enter A confirmation message appears asking if you want to modify any of the entries 3 Press Enter again to save the changes to the driver From within the driver page you can view open the URL for the driver manufacturer to read detailed information on the DB driver by selecting F7 Driver page To restore the default settings of the current driver select F9 Restore Default DB Gate User Manual Version 1 5 Chapter 3 DB Drivers 14 Modify DB Driver Driver WB2 for Windows Linux Driver file iSecurity DB Gate Drivers db2 jcc jar Class com ibm db2 jcc DB2Driver Driver page http www 306 ibm com software data db2 java Default port S000 Additional parms Label Possible values Parm 1 lt adl1 gt Parm 2 lt adl 2 gt Parm 3 lt adl 3 gt Parm 4 lt adl 4 gt URL dft schema jdbc db2 lt host gt lt port gt lt db gt Std replacments lt host gt lt db gt lt catal og gt lt port gt lt schema gt lt adl 1 4 gt F3 Exit F7 Driver page F9 Restore Default Fi2 Cancel Modify DB Driver Screen The following table describes the information in the DB driver screen Parameter Description Driver Driver s name and description It will appear in the DB Drivers list when configuring a connection
23. jids DIR Parameters or command gt F3 Exit F4 Prompt F5 Refresh F9 Retrieve F12 Cancel Fi7 Position to F22 Display entire field F23 More options Work with Object Links Screen The drivers belong to the official owners They are supplied alongside the product for convenience Next to each driver you may find the actual license under which it is supplied These drivers have been downloaded and are supplied by Raz Lee with the understanding that they may be supplied in this way DB Gate User Manual Version 1 5 Chapter 3 DB Drivers 18 Chapter 4 DB Gate Log DB Gate s Display Log shows the contents of the history log which saves various data gathered from the different directory entries as you have defined them in a standard format and using basic filter criteria The Backward Glance feature lets you look at the last several minutes of activity without the need to define specific time or date parameters To begin filtering log entries select 41 Display Log from the main menu The following Display DB Gate Log Entries screen is displayed Display DB Gate Log Entries Type choices press Enter Display last minutes RIBYT IME Starting date and time Starting date CURRENT Starting time 000000 Ending date and time Ending date ee CURRENT Ending time a e a ee ee ee 235959 User profile XALL Remote user profile XALL Relational DB Entry XA
24. ly the first FETCH is logged F3 Exit Fi2 Cancel General Definitions Screen NOTE When you select the 0 Global default setting for a specific directory entry it will take the setting you define on this screen 3 When you are done press Enter After the confirmation message appears press Enter again to save your settings DB Gate User Manual Version 1 5 Chapter 7 System Configuration 28 Log Retention Setting DB Log Retention Parameters Log Retention parameters govern the retention and backup of the DB Gate history log files In order to preserve desk storage capacity and improve query response time you should retain transactions for the minimum period necessary to maintain an effective audit program The recommended initial settings are shown below To set the log retention periods do the following l Select 9 Log Retention from the DB Directory Entry System Configuration screen The following DB Log Retention screen appears Enter the parameters as defined in the table below When you are done press Enter After the confirmation message appears press Enter again to save your settings DB Log Retention 25 03 12 10 40 10 Type options press Enter Data retention period days Days 99 xNOMAX Backup program for data Name XSTD NONE Backup program library You may specify a backup program to run automatically before deleting old data This program runs prior to automatic deletion of data
25. n system configuration Select 81 System Configuration from the main screen and press Enter The following DB Directory Entry System Configuration screen appears DBPARMR DB Directory Entry System Configuration 25 03 12 12 18 36 Select one of the following DB Gate 1 General Definitions 9 Log Retention General 98 Driver Licenses Selection gt E 99 Copyright Notice Release ID HIH 12 03 20 44DE466 520 7459 Authorization code BO1203731542 1 F3 Exit F22 Enter Authorization Code Fi2 Cancel DB Directory and System Configuration Screen NOTE After you modify any of the parameters accessible from this menu the message Modify data or press Enter appears upon return to the menu B Gate User Manual Version 1 5 Chapter 7 System Configuration 27 General Definitions To change the global settings of DB Gate do the following 1 Select 1 General Definitions from the System Configuration screen The following General Definitions screen appears 2 Enter a Log level setting from one of the following options Option Description 2 Connect The log will store a record of each connection and disconnection from a database The log will store all commands sent when a Fetch command is sent it will only store the first in the series General Definitions Type options press Enter Log level 2 Connects only 4 Al Notes All errors are logged regardless of this setting For 4 All on
26. or interactive Structured Query Language SQL From it you can type and run SQL statements SQL messages also appear on this screen B Gate User Manual Version 1 5 Chapter 6 Test Drive 25 Working with SQL Program Sources To begin working with SQL Program Sources select 55 Work with SQL program sources from the main screen and press Enter The following Work with Members Using PDM screen is displayed Work with Members Using PDM WRKMBRPDM Type choices press Enter Pile soson 4 ow Sa EE name Library gt gt SZ NS a name XLIBL xCURLIB Member 2 2 2 7 ee ew ee name Xgenericx Member type type Xgenericx Bottom F3 Exit F4 Prompt F5 Refresh F12 Cancel Fi3 How to use this display F24 More keys Work with Members Using PDM Screen DB Gate User Manual Version 1 5 Chapter 6 Test Drive 26 Chapter 7 System Configuration DB Gate is ready to run right out of the box You should review a few system configuration parameters that control important features prior to using the product for the first time It should be pointed out that there is no typical or optimal configuration for a connectivity product such as DB Gate Each installation or application has different operational criteria and security needs For example the log requirements for a large manufacturing environment may be quite different from those for a bank a software developer or a service organization To begi
27. pported E Database large objects BLOBs CLOBs DBCLOBs E Data links E User IDs longer than ten characters E Passwords longer than ten characters E Stored procedure result sets E SQL statements longer than 32K E Stored procedures with Commit on Return E Scrollable cursors E Multi row input E Extended diagnostics E RDB aliases DB Gate User Manual Version 1 5 Chapter 1 Introducing DB Gate Note about Commitment Control When using an ARDPGM the system enforces the use of commitment control If for example session attributes for commitment control is set to NONE the system will change this setting immediately after the connection An RDB entry that is based on ARDPGM is always considered as a remote connection and thus can not be assigned NONE or NC for commitment control Commitment Control Help Select the type of commitment control The possible values are NONE or NC Specifies that commitment control is not used Uncommitted changes in other jobs can be seen If the SOL DROP COLLECTION statement is included in the program NONE or NC must be used If a relational database 1s specified on the RDB parameter and the relational database 1s on a system that 1s not on an 1Series NONE or NC cannot be specified Native OS 400 Text Based User Interface DB Gate is designed to be a user friendly product The user interface follows standard System 1 CUA conventions All product features are available via
28. r specific library of the program name No Maximum amount NOMAX or a specific number On the screen Printed on one of the defined printers If you enter Outfile you must then define the following additional parameters File to receive output Library Member to receive output Replace or add records Once you have defined the filter for the log press Enter to view the results The following screen is an example of the output displayed DB Gate User Manual Version 1 5 Chapter 4 DB Gate Log 21 Display DB Gate Log Log information is available from 22 03 12 9B gt DB gt DB gt SASHA SASHA SASHA DB gt DB gt SASHA SASHA SASHA DB gt SASHA WAREHOUSE PREPARE SQLERR 204 SQL0204 DEMOPF21 in ILAN type xFILE not WAREHOUSE PREPARE SQLERR 104 SQL0104 Token FROM1 was not valid Valid WAREHOUSE PREPARE SQLERR 204 SQL0204 DEMOPF21 in ILAN type xFILE not gt CRM PREPARE SQLERR 1146 Table ilan demopf21 doesn t exist select x gt CRM PREPARE SQLERR 1146 Table ilan demopf21 doesn t exist select x gt CRM PREPARE SQLERR 1 Table ilan demopf21 doesn t exist select x f WAREHOUSE PREPARE SQLERR 204 SQL9204 DEMOPF21 in ILAN type xXFILE not WAREHOUSE PREPARE SQLERR 204 SQL0204 DEMOPF21 in ILAN type XFILE not gt CRM PREPARE SQLERR 204 Table ilan demopf21 doesn t exist select x gt CRM PREPARE SQLERR 204 You have an error in your SQL syntax check t gt CRM PREPARE
29. ral Configuration and Maintenance B Gate User Manual Version 1 5 Chapter 1 Introducing DB Gate 4 DBMENU DB Gate iSecurity System 520 Select one of the following Patent Pendingx Remote DB Definitions Activity Log 1 Work with Directory Entries 41 Display Log DB Drivers Test Drive 11 Work with DB Drivers 531 Start SOL 15 Drivers amp Licenses Folders 99 Work with SQLk program sources Auto Injection of Remote User ID General 21 Work with Server Authentication 81 System Configuration 82 Maintenance Menu Selection or command gt F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Information Assistant F16 AS 400 main menu Main DB Gate Screen Why You Need DB Gate IBM AS 400 users often require access to remote non DB2 databases that do not support DRDA server behavior such as Oracle MS SQL and Derby These databases run on various operating systems and use standard languages 1 e SQL Typical Remote Access Limitations To successfully access remote databases companies have selected products based on either of the following limited methods E Using ambiguous or non standard APIs that are not defined through RDBDIRE relational Database Directory Entry and therefore make no use of an ARD program E Selecting a client server configuration that requires additional hardware These options are cumbersome require special settings which often prevent users from fully utilizing standard SQL commands on the
30. roup has full access Read and Write to all the product s components You may add more operators delete them and give them authorities and passwords according to your own judgment You even have the option to make the new operators definitions apply to all your systems therefore upon import they will work on every system To view existing operator s authorities and modify them do the following From the Maintenance Menu select 11 Work with Operators Work with Operators Type options press Enter 1 Select 4 Delete Authority level 1 xUSE 9 xFULL Opt User System DB Adm xAUDHSECAD 520 9 9 CS 520 H GS 520 9 9 OS 520 1 1 Bottom DB DB Gate Adm Administrator F3 Exit F6 Add new F8 Print F11 kSECADM xXAUDIT authority F12 Cancel Work with Operators To modify an operator highlight it and choose 1 Select To add a new operator press F6 DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu 33 Modify Operator Type choices press Enter Operator XAUD SECAD System 2 se 520 Password XSAME Authorities by subject DB Gate se i Product Administrator F3I Exit Fi2 Cancel XALL Name Name XSAME XBLANK 1 xKUSE 9 xFULL 1 KUSE 9 xFULL Modify Operator Screen DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu 34 Parameter Description Operator The Operator can be a user or group profile DG Gate provides one type of Opera
31. s press Enter From DAFFODILSR Description Daffodil DB Server To New Driver AFFODILSR F3 Exit F4 Prompt Fi2 Cancel Copy DB Driver Screen DB Gate User Manual Version 1 5 Chapter 3 DB Drivers 16 Adding a New DB Driver To add a new DB driver do the following 1 From the Work with DB Drivers screen press F6 An empty Add New DB Driver screen appears After you complete the fields press Enter After the confirmation message appears asking if you want to modify any of the entries press Enter again to save the new DB driver Deleting a DB Driver To delete a DB driver from the list do the following 1 Select the DB driver and then select 4 Delete The driver s page is displayed 2 Press Enter to confirm the delete request The driver is deleted DB Gate User Manual Version 1 5 Chapter 3 DB Drivers 17 Drivers amp Licenses Folders To view and update a connection to a remote database select 15 Drivers amp Licenses Folders from the main screen The Work with Object Links screen opens This resource screen lets you see the paths to the third party drivers and their licenses Work with Object Links Directory iSecurity DB Gate Drivers Type options press Enter 2 Edit 3 Copy 4 Remove 3 Display 7 Rename 8 Display attributes 11 Change current directory Opt Object ink Type Attribute Text DIR DIR db2 DIR derby DIR excel DIR Firebird DIR hsql db DIR h2sql DIR
32. ss to and from the System 1 within or outside the organization under all types of communication protocols Firewall manages user profile status secures entry via pre defined entry points and profiles activity by time Its Best Fit algorithm determines the validity of any security related action hence significantly decreasing system burden while not compromising security Audit Audit is a security auditing solution that monitors System i events in real time It includes a powerful query generator plus a large number of predefined reports Audit can also trigger customized responses to security threats by means of the integrated script processor contained in Action Action Action automatically intercepts and responds to security breaches system activity events QHST contents and other message queues Inquiring messages can be automatically answered Alerts are sent by e mail SMS pagers or the message queues Command scripts with replacement variables perform customized corrective actions such as terminating a user session or disabling a user profile View View is a unique patent pending field level solution that hides sensitive fields and records from restricted users This innovative solution hides credit card numbers customer names etc Restricted users see asterisks or zeros instead of real values View requires no change in existing applications It works for both SQL and traditional I O Screen Screen protects una
33. task and return to the screen or menu from which the task was initiated F4 Prompt Display a list of valid options for the current field or command For certain data items a pop up selection window appears New Features and Functionality Version 1 5 E Support added for NVARCHAR field type E Enhancements made to embedded SQL in RPG E Added Bidi String Type option 1 NONE in RDB entry screen E The product is bundled with a new JDBC driver for MS Excel 97 2000 XP 2003 CSV ODF spreadsheet LibreOffice OpenOffice and text files This driver is an in house development See Appendix A for a description of how to use it DB Gate User Manual Version 1 5 Chapter 2 Remote DB Definitions This chapter describes how to define a remote database When definitions are activated they create an RDBDIRE Remote DB Directory Entry in the operating system Work with Directory Entries To begin working with directory entries select 1 Work with Directory Entries from the main screen The following Work with Directory Entries screen opens Directories are marked as Active or deactivated Work with Directory Entries Type options press Enter Subset 1 Select 3 Copy 4 Remove Activate 8 Deactivate Opt Status Directory entry Active CRM Active SALES zer SHIPMENTS Active TESTDB Bottom F3 Exit F5 Refresh F6 Add new F8 Print Fi2 Cancel DB Directory Entry deleted Relational database directory entry deactivated
34. tion Assistant F16 AS 400 main menu End Journal Confirmation Screen DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu iSecurity DB Gate 38 Displaying a Journal To view journaled files select option 79 Display Journal from the Maintenance Menu screen The following Display Journal Entries screen appears Display Journal Entries Journal SMZ4 Library SMZ4DTA Type options press Enter 5 Display entire entry Code J Opt Sequence 1 2 3 4 5 6 7 8 Ei Teor Tt ER ato H 9 Teo F3 Exit Fi2 Cancel Uninstall Type PR JF JM JF JM JF JM JF JM JF JM JF Object AUACTION2P AUACTION2P AUACTNL AUACTNL AUACTNP AUACTNP AUADPAP AUADPAP AUADPLP AUADPLP AUAUDOP Display Journal Entries Library SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA SMZ4DTA Job QPADEVOOOV QPADEVOOOYV QPADEVOOOV QPADEVOOOYV QPADEVOOOV QPADEVOOOV QPADEVOOOV QPADEVOOOV QPADEVOOOV QPADEVOOOYV QPADEVOOOV QPADEVOOOV Use the Uninstall feature to remove DB Gate from your computer Select 91 Uninstall from the Maintenance Menu and follow the directions that follow on the Uninstall SECURITYBP screen DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu Appendix A JDBC Driver for Excel CSV ODS and Text Files Beginning from version 1 5 DB Gate includes an internal JDBC driver for MS Excel 97 2000 XP 2003 comma separated valu
35. tor group AUD SECAD This group has full access Read and Write to all parts of DB Gate Password Name Password GAME Same as previous password when edited BLANK No password DB Gate User of the product 1 USE Read authority only 9 FULL Read and Write authority Product Administrator Person responsible for backups setting authorization codes etc 1 USE Read authority only 9 FULL Read and Write authority Check Locks To verify if objects are locked in the system select 52 Check Locks from the Maintenance Menu DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu GSLCKMNU Check Locks iSecurity System 320 Select one of the following Check Locks 1 Data Base Files Display Files End this session Enter CHKSECLCK OBJTYPE xXDSPF from a new session All File Types End this session Enter CHKSECLCK OBJTYPE xALL from a new session Selection or command F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Information Assistant F16 AS 400 main menu Check Locks Screen Add DB Gate to Java CLASSPATH Use this tool if you suspect that the Java components required by the system cannot be found Using it will define the location of the required components in the CLASSPATH which is similar to LIBL for Java DB Gate User Manual Version 1 5 Chapter 8 Maintenance Menu Journal Definition Files A journal file records changes made to definitions in the data library Adding
36. ttended terminals and PC workstations from unauthorized use It provides adjustable terminal and user specific timeout capabilities Password Password provides a first tier wall of defense for users by ensuring that user passwords cannot be easily cracked Assessment Assessment checks your ports sign on attributes user privileges passwords terminals and more Results are instantly provided with a score of your current network security status with its present policy compared to the network if iSecurity were in place DB Gate User Manual Version 1 5 About This Manual 3 Chapter 1 Introducing DB Gate Raz Lee Security s DB Gate part of the iSecurity suite lets you open concurrent and transparent SQL connections to numerous types of remote data sources and databases You can do this on any IBM or non IBM platform using STRSQL and compile using CRTSQL for programs which refer to these databases DB Gate is a client only product so it saves you both money and time you would otherwise require to integrate new hardware or set up a complicated API System Requirements DB Gate requires the V5R3 operating system or higher Starting DB Gate To begin using DB Gate type STRDB on any command line The main screen is displayed You can access the different features from the main DB Gate screen The various feature groups are described in the following chapters E Remote DB Definitions E DB Drivers E Activity Log E Gene
37. ty you want to view without the need to define any other time or date parameters The feature is particularly helpful when trying to clarify the cause of problems Starting date Choose from Current Start Yesterday Week Start Previous Weeks Month Start Previous Months Year Start Previous Years and each day of the week Starting time 00 00 00 format Ending date Same options as Starting date User profile All default or a specific user profile on the AS 400 Remote user profile All default or a specific user profile on the remote database Relational DB Entry All default or a specific remote DB entry DB Gate User Manual Version 1 5 Chapter 4 DB Gate Log Parameter SQL Operation SQL State SQL Error Code SQL Statement contains SQL Message contains From job name From program name Number of records to Description All default or a specific SQL operation that is used at the beginning of an SQL statement e g CONNECT CREATE DROP etc All default or a specific state All default a specific error code number or name that describes the problem or entries without any error code All default or a specific SQL statement All default or a specific error message that is associated with an error code All default or specific job All default or specific user All default or a specific number All default or the program in the AS 400 that created this log All default o
38. w Directory Entry by copying one that already exists Copying a directory entry is also the only way you can change the name of an existing directory entry 1 To copy an existing directory highlight it and select 3 Copy The Copy DB Directory Entry screen appears with the selected directory entry already filled in 2 Enter a new Directory Entry name and press Enter then press Enter again to confirm your new entry All the existing directory entry details are automatically added and the Work with Directory Entries screen is redisplayed with the newly created directory entry deactivated Creating a New Directory Entry You can choose to add a new Directory Entry either by copying one that already exists or by entering all the details on your own To add a completely new directory entry do the following 1 Select F6 Add New The following Add New Directory Entry screen is displayed 2 Type the new Directory Entry alias name You can also select F4 Prompt when in the Directory Entry field to display the existing Directory Entry list If you choose one and press Enter it will then be added to the new Directory Entry field 3 You must now add an existing driver type Either type it in or select F4 Prompt when in the Driver field to display a list of the existing DB drivers 4 Click Enter to move on to the empty Add New Directory Entry Details screen If the URL had been saved in the driver it is automatically added This screen
39. when issuing the SELECT statement the QTEMP library must be specified SELECT FROM QTEMP MY_TABLE When completed the file QGPL MY_TABLE is created and available with the data B An SQL SELECT statement SELECT FROM QGPL MY_TABLE DB Gate User Manual Version 1 5 Appendix A JDBC Driver for Excel CSV ODS and Text Files 41 The driver accesses the source PC file in a read only manner Access Protocols Examples Function Key Description File Mainly for accessing files in the IFS file system COMMENT ON QGPL MY_TABLE IS file tmp customers xls HTTP Mainly for the web COMMENT ON QGPL MY_TABLE IS http www razlee info gui db_gate ms xls FTP COMMENT ON QGPL MY_TABLE IS ftp myserver com readme txt SMB MS Windows shares COMMENT ON QGPL MY_TABLE IS smb 192 168 1 181 shareddocs sales csv DB Gate User Manual Version 1 5 Appendix A JDBC Driver for Excel CSV ODS and Text Files 42
Download Pdf Manuals
Related Search