DB-Gate v2.1 User Manual - Raz-Lee
Contents
1. Record your product authorization code here DB Gate User Manual li Table of Contents CO CUTE IN ONC cisco secs a a aai il Table OF CONTCIS sirnaa EERE iii About This Manual sac iscscasecasetncxccsicawsseensexseusccessevisensnsrancnnteacavsctemecnenecsenets 1 Product Documentation Oy CEVICW sessevvasdescapsesdenseteiuerendecaactebontemebesteesdaraesecstensnileaadieds l eae Uy VE a E E E E E E EESE T E A E E ene seen eeee l Onne He e N E EN AA AAE AE EEES l Typography ONY CIOS sv nissccgnsncrezanesabyssaadaueneasunentebeerctobeseacs EN ETAT EE ENT e ENER 1 TSC Uy Product SUE saniran E EE 2 OV OPV ICN onne RE E EE EEEE EEE 5 System ReguireMEN S soso ceca csesc0ncsoacaasvoneeeararaassssaaaeanerageaepecseosset E a REA e 5 I OA O a E A E Set EARO 3 Why you Need DB Gate sc cvcansnrevarsanaseconsetesensadsaiqseseeaecsuoenenedeebtineneometedennnniiicenenteevexeduentcanades 7 Typical Remote Access Limitations ccccccccc ccc cece aaa a aaa a aaa aeseeaeaas 7 DEE ee e E E A A E A A E EA E A T 7 WES APS AR SS ICU 15 E 8 COMMENT COMM 0 a E E N E E e 8 Native OS 400 Text Based User Interface ccc ccccccsccseceseeeeeeeeeeceeeeeeeeeeeseseeeeeeeeeeegs 9 New Features and Functionality ccccccccccessscsssesesssssaaeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeseeeeeeeegs 10 PAGUIN ARI OI iriiria aiana aSa AAA RRRA DAE 11 OD e E AT A E E E A A een 11 Mode T ANE ee a E E EEA AN E EA E E IEE A EN EGA 12 A O E E E Peta tvaen qo ae een tees2. Product Description 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 conjunction 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 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 i 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 re within texts DB Gate User Manual 2 iSecurity Product Suite Product AP Journal Anti Virus Visualizer Firewall DB Gate User Manual Description AP Journal automatically manages database changes by documenting and reporti
3. Use the Uninstall feature to remove DB Gate from your computer 1 Select 91 Uninstall from the Maintenance Menu 2 Follow the directions that follow on the Uninstall SECURITYBP screen Uninstall SECURITYBP You are about to uninstall this product All program files data and definitions will be deleted You are advised to print this screen for further reference Before proceeding ensure that o No user or batch job is working or intends to work with this product To run uninstall procedure you should do the following o Exit from the current session o Open a new session using QSECOFR or equivalent user profile o Enter CALL SMZB DBRMVPRD Once the uninstall is completed enter DLTLIB SMZB Manually delete IFS directory iSecurity DB Gate Backups of previous releases might exist under the name QGPL P_SM2Zx To confirm proper uninstall use DSPUSRPRF SECURITYBP TYPE xOBJOWN Figure 10 6 Uninstall SECURITYBP Screen DB Gate User Manual 54 Troubleshooting This chapter describes various issues that may arise and how to handle them Please review the following sections prior to contacting your Raz Lee distributor or customer support Issue Java versions lower than v5 0 loaded by default Workaround DB Gate requires Java v 5 or higher to run If any job has an earlier version of Java loaded perform the following workaround running either E Mode 1 Inline E Mode 2 Internal Server recommended Mode 1
4. is a MS SQL Fi2 Cancel Figure 4 6 DBG Add New Directory Entry 3 Add an existing driver type either by typing it or using the 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 This screen is the same as the Modify Directory Entry screen above as shown in Figure 4 3 on page 23 NOTE If the URL is already saved in the driver it is automatically added Removing a Directory Entry To remove a Directory Entry 1 In the Work with DB Directories screen select the directory to be removed by typing a 4 next to it and pressing Enter The Delete Directory Entry Part A screen appears with the Directory Entry details 2 Press Enter and then again Enter when the Delete Directory Entry Part B screen appears to confirm The Work with DB Directory Entries screen reappears with the Directory Entry removed DB Gate User Manual 26 Activating a Directory Entry Activating a Directory Entry To activate a Directory Entry 1 In the Work with DB Directories screen select the directory to be removed by typing a 7 next to it and pressing Enter 2 The Directory Entry is activated Deactivating a Directory Entry To deactivate a Directory Entry 1 In the Work with DB Directories screen select the directory to be removed by typing a 8 next to it and pressing Enter 2 The Directory Entry is deactivated
5. 1 Copy the file iSecurity DB Gate sp properties to iSecurity DB Gate SystemDefault properties 2 Update the Home Directory for the user profile to your own unique IFS folder For example to modify the SECURITYBP user profile type CHGUSRPRF USRPRF SECURITYBP HOMEDIR iSecurity DB Gate 3 Run the command sequence STRDB gt 81 gt 1 4 Turn off Auto Set Java version and CLASSPATH by changing the status to N 5 Make sure that the SystemDefault properties file contains the line java version 1 5 6 Deactivate and then reactivate the server Error Messages in Server Mode The following error messages may appear when running in server mode DB Gate messages in server mode Message DB Gate server disconnected Reason DB Gate has disconnected from running SQL job due to activation group end job end or explicit disconnect request for that job Release Job from GUI Message No response from DB Gate server Reason DB Gate server is not currently active Message Connection terminated Job should be restarted Possible Reasons E DB Gate server has been deactivated while SQL job was working with DB Gate This doesn t mean DB Gate server is not currently active but any connection made with DB Gate until that message is lost It is recommended to restart the job E Error in the Java engine of DB Gate DB Gate User Manual 56 Appendix A JDBC Driver Adding an Entry To add an entry 1 Typ
6. 1 DB Driver Parameters and Commands Parameter annan Description 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 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 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 DB Gate User Manual 32 Copying a DB Driver Copying a DB Driver To copy a DB driver do the following 1 Select the DB driver you want to copy press 1 Copy and then press Enter The Copy DB Driver screen opens with the name and description of the selected driver as shown in Figure 5 4 on page 33 In the new Driver field change the name and then press Enter 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 choices press Enter From DAFFODILSR Description Daffodil DB Server T
7. 12 Mode 2 SHICHI a Servei serora ARENASA EARS 12 BETONT a E E A A EA 12 A TT OW a E EAE E AAE AE pte 13 Automatic Activation upon Startup eesssassssseeesessssssenennenssssssssserererssssssesseeeees 15 Mode ss Frema fc e I E EE ANE OENES 15 Running the DB Gate Servet nnnnnnnnnnnnnnnnesssssssssssssssrsssrrrssrrrrrresrreeereee 17 DPDB Gale Servar COMMONS isiin E a a ind eaaaieannee eens 18 Changing the DB Gate Mode of Operation ccccccccccsccsseccceececceeeeeeeeeeeeessseeeeeeeeeeeegs 19 Pala OS CS sir sosaanc E E 20 Remote DB Definitions cccccceeeeeeeeeeeeeeeeeeeeeeeeeeneeeeeneeeeneesennenseneees 21 Working with Directory Entries serisinin nn SE S A S 21 Viewing Subsets of Drivers scscccnsssscccsssnateteccasorsdancevassnduteaseccmraanccienseocinsaacubaasneedensnadnsenuioner 22 Modifying a Directory Entry s 0 2sdscseduterestodeveatanaanshtheueeddneslondthetionsabeaisrad iiei iais 23 Copying an Existing Directory Entry ososeseeesnnnnenseenenreressssssssssssssssssssssssssserrsrerrrrrrrren 25 Creating a New Directory Entry ss ccsicasaccosssnasenonascnecsnnnncetassadentanwcaneedeveaseastapsecdtenseectenssestes 26 Removing a Directory ENUY ccscccsncsstssncnstanacsicacadasios ca aai aiiai adiens 26 Activan a Directory Eny siprisssorss nnne E ne ceea cau eaoser 27 Deactivating a Directory Entry cccccccsssssssccsssseeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeneeges 2 DB Gate User Manual DB V
8. DB Gate User Manual 27 Deactivating a Directory Entry DB Gate User Manual 28 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 DB Gate comes with pre installed DB drivers The default path for drivers 1s iSecurity DB Gate Drivers You can enter a full path or a path relative to DB Gate default driver s location as shown in Figure 5 1 on page 29 Modify DB Driver Driver Driver file ORACLE_SID Oracle SID iSecurity DB Gate Drivers oracle ojdbcsS jar Class oracle jdbc Oracl eDriver Driver page http wuw oracle com technol ogy tech java sql j_jdbc index h tml 1521 Label System ID Default port Additional parms 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 Std replacement lt host gt lt db gt lt catal og gt lt port gt lt schema gt lt adl 1 4 gt Possible values DB instance XE jdbc oracle thin lt host gt lt port gt lt adl 1 gt F3 Exit F7 Driver page F9 Restore Default F12 Cancel Modify DB Driver Driver Driver file Class Driver page Default port Additional parms Parm 1 lt adl 1 gt Parm 2 lt adl 2 gt Parm 3 lt adl 3 gt Parm 4 lt adl 4 gt URL dft schema Std replacement lt host gt lt db gt lt catal og gt lt port gt lt schema gt
9. Enter The Work with Members Using PDM screen is displayed as shown in Figure 8 2 on page 44 Work with Members Using PDM WRKMBRPDM Type choices press Enter Fale 4 sa va 2 amp Ww amp RPRV name Library 4 s gt a s a wss u name XLIBL xCURLIB Member e cessas eee XALL name Xgenericx Member type gt SQLK type Xgenericx Bottom F3 Exit F4 Prompt F5 Refresh F12 Cancel Fi3 How to use this display F24 More keys Figure 8 2 Work with Members using PDM DB Gate User Manual 44 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 begin system configuration select 81 System Configuration from the main screen and press Enter The DB Directory Entry System Configuration screen appears as shown in Figure 9 1 on page 45 DBPARMR DB Directory Entry System Configuration 25 03 12 12 18 36 Select one of the following DB Gate 1 General Definitions 9 Log Retent
10. F12 Cancel F13 Information Assistant F16 AS 400 main menu Figure 10 1 Maintenance Menu DB Gate User Manual 49 Display DB Gate Definitions A Display DB Gate Definitions To print the definitions that you entered for DB Gate 1 From the Maintenance Menu screen select 5 Display DB Gate Definitions The Display DB Gate Definitions screen appears as shown in Figure 10 2 on page 50 Table 10 1 on page 50 explains the options Table 10 1 DB Gate Definition Options Parameter Value Description Report Type ALL All of the following options DBENTRY The definitions for the Directory Entries DBDRVR The definitions for the DB Drivers Start From the beginning of all values Pe m a ee a 00 2 When you are done press Enter 3 After the confirmation message appears press Enter again to save your settings Display DB Gate Definitions Type choices press Enter Report type gt XDBENTRY XALL XDBENTRY xDBDRVR From item BALL Character value XALL XxSTART To item Character value XONLY XxLAST Format xXDETAILS XLIST xDETAILS x XPRINT Bottom F3 Exit F4 Prompt F5 Refresh F12 Cancel Fi3 How to use this display F24 More keys Figure 10 2 Display DB Gate Definitions Screen DB Gate User Manual 50 Work with Operators 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
11. Fi2 Cancel Figure 4 3 Modify Directory Entry Screen Table 4 1 on page 23 describes the detailed information you enter to create a new RDB Entry Some of the parameters may vary depending on the selected driver Table 4 1 RDB Entry Parameters Parameter Description Options Directory Entry Name of the directory Y 78 or N Tro DB Gate User Manual 23 Modifying a Directory Entry pa Table 4 1 RDB Entry Parameters Parameter Description Options The driver you associated with the new entry Description of the directory optional 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 L Provided by the DB driver provider May include parameters UR lt host gt lt ip gt lt user gt lt pwd gt lt catalog gt lt port
12. If the SQL DROP COLLECTION statement 1s 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 Figure 2 2 Commitment Control Help DB Gate User Manual DB Gate Restrictions 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 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 Pop up selection windows m Convenient option prompts Easy to read descriptions and explanatory text for all parameters and options m Search and filtering with generic text support The following describes the different
13. Inline The following steps will impact on all Java programs running in every job 1 Copy the file iSecurity DB Gate sp properties to iSecurity DB Gate SystemDefault properties 2 Update the Home Directory for the user profile to your own unique IFS folder Modify the desired user profile by typing the following command CHGUSRPRF USRPRF xxx HOMEDIR iSecurity DB Gate 3 Run the command sequence STRDB gt 81 gt 1 4 Turn off Auto Set Java version and CLASSPATH 5 Make sure that the file SystemDefault properties contains the line java version 1 5 General Definitions Type options press Enter 1 Inline no pre activation 2 Internal server 3 External server For Run mode 2 set any password to user profile SECURITYBP to enable the initiation of the Internal Server This password does not have to be entered anywhere in the product SQL Activities are always done by the actual user authority Auto Set Java version and CLASSPATH N Y Yes N No Java 1 5 or higher required See CLASSPATH in iSecurity DB Gate sp properties Log level errors are always logged 5 1 No Log E 2 Connects only 4 AIl only first FETCH logged F3 Exit F12 Cancel Figure 11 1 General Definitions Screen Mode 1 Options 6 Start a new job to test DB Gate User Manual 55 Error Messages in Server Mode E Mode 2 Internal Server It is recommended to use Mode 2 since only the internal DB Gate user will be affected
14. Screen d Upon completion select Launch DB Gate Server and click Finish ie Setup DB Gate Server p O x Completing the DB Gate Serwer Setup Wizard Setup has finished installing DB Gate Server on your computer The application may be launched by selecting the installed icons Click Finish bo exit Setup M Launch DB Gate Server Figure 3 8 DB Gate Server Setup Wizard Finish Screen The Connect to System dialog appears See Running the DB Gate Server on page 17 DB Gate User Manual 16 E Mode 3 External Serve E Running the DB Gate Server The DB Gate server is started either by Selecting Launch the DB Gate server at the end of the installation process m Starting the DB Gate server from the Windows Start menu or the icon 1O x Start Server top Serve Figure 3 9 DB Gate Server Startup Screen 1 Click Start Server The Connect To System dialog appears See Figure 3 10 on page 17 2 Connect to the system as follows a Type the address IP or network path or host name for the DB Gate server b Type the User and Password to access that host c Click OK FA connect To System a x System P Address or Mapped Host Name User A5 400 User Use SSL Ok Cancel Figure 3 10 Connect to System Dialog 3 Login to the IBM 1 4 Start SQL by typing STRSQL 5 Connect to the SQL database instance as shown in Figure 3 11 on page 18 DB G
15. 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 An SQL SELECT statement SELECT FROM QGPL MY_TABLE The driver accesses the source PC file in a read only manner Excel sheets can be accessed by specifying the sheet number in the COMMENT ON statement Example COMMENT ON QGPL MY_TABLE IS file tmp customers xls 2 On this example the file is customers xls and the query targets the second sheet 2 The first sheet is 1 Tables embedded in ODF Text Documents LibreOffice OpenOffice can be accessed in the same manner as sheets on Excel spreadsheet Table A 1 Access Protocols Examples Function Key Description 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 MS Windows shares COMMENT ON QGPL MY_TABLE IS smb 192 168 1 181 shareddocs sales csv DB Gate User Manual 58
16. data Name XSTD XxXNONE 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 whenever the retention period expires The XSTD program is SMZB DBSOURCE DBDBGBKP F3 Exit Fi2 Cancel Figure 9 3 DB Log Retention Table 9 1 DB Log Retention Parameters 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 Enter the name of the backup program you wish to use data 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 47 DB Gate User Manual 48 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 The Maintenance Menu appears as shown in Figure 10 1 on page 49 DBMINTM Maintenance Menu iSecurity DB Gate System 520 Select one of the following xPatent Pendingx Definitions General 3 Dispaly DB Gate Definitions 32 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 Uninstall 91 Uninstall Selection or command F3 Exit F4 Prompt F9 Retrieve
17. is an example of the output displayed Display DB Gate Log Log information is available from 22 03 12 DB gt WAREHOUSE PREPARE SQLERR 204 SQL0204 DEMOPF21 in ILAN type xFILE not DB gt WAREHOUSE PREPARE SQLERR 104 SQL0104 Token FROM was not valid Valid DB gt WAREHOUSE PREPARE SQLERR 204 SQL0204 DEMOPF21 in ILAN type xFILE not SASHA gt CRM PREPARE SQLERR 1146 Table ilan demopf21 doesn t exist select x SASHA gt CRM PREPARE SQLERR 1146 Table ilan demopf21 doesn t exist select x SASHA gt CRM PREPARE SQLERR 1 Table ilan demopf21 doesn t exist select x f DB gt WAREHOUSE PREPARE SQLERR 204 SQL9204 DEMOPF21 in ILAN type xXFILE not DB gt WAREHOUSE PREPARE SQLERR 204 SQL9204 DEMOPF21 in ILAN type xXFILE not SASHA gt CRM PREPARE SQLERR 204 Table ilan demopf21 doesn t exist select x SASHA gt CRM PREPARE SQLERR 204 You have an error in your SQL syntax check t SASHA gt CRM PREPARE SQLERR 204 You have an error in your SQL syntax check t DB gt WAREHOUSE PREPARE SQLERR 104 SQL0104 Token FROM was not valid Valid SASHA gt CRM PREPARE SQLERR 204 You have an error in your SQL syntax check t Bottom F3 Exit F1Q Entire Entry Fi7 Top F18 Bottom Figure 6 3 Display DB Gate Log To view further details on the output select F10 Entire Entry The following screen appears Additional Entry Information System 520 Remote DB Entry WAREHOUSE From User
18. mode now works with single data queue This data queue can be recreated via the Activation menu PC installer for mode 3 DB Gate as an external server Can run as an internal server on the IBM 1 thereby eliminating the JVM starting time Can run as an external server on a PC Unix or other platform supporting Java rather than using resources on the IBM 1 Keep alive statement is sent repeatedly Support for SMB protocol to allow access to Excel CSV files on shared folder Added support for reading Excel 2007 2010 XLSX formats Running on a PC enables DB Gate to work with additional data sources by employing the JDBC ODBC bridge That means for example that we can get data from MS Access Enhancements made to better support embedded SQL in RPG COBOL Added Bidi String Type option 1 NONE in RDB entry screen The product is bundled with a new JDBC driver for MS Excel 97 2000 XP 2003 2007 2010 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 Support added for NVARCHAR field type DB Gate User Manual 10 Activation Overview DB Gate can be activated to run in either Mode Inline Running in the same job which requires external DB access Mode 2 Internal Server Running on a separate subsystem in the IBM 1 This server supports any number of jobs E Mode 3 External Server Running in an external syste
19. 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 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 Menu options field names and function key names are written in Courier Bold References to chapters or sections are written in Italic OS 400 commands and system messages are written in Courier Key combinations are separated by a dash for example Shift Tab Emphasis is written in Times New Roman bold DB Gate User Manual 1 iSecurity Product Suite ES iSecurity Product Suite 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 The iSecurity Product Suite includes
20. 5 5 Work with Object Links 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 34 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 DB Gate User Manual 35 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 BBY TIME Number XBYTIME Starting date and time Starting date CURRENT Date xXCURRENT xXSTART Starting time 900000 Time Ending date and time Ending date CURRENT Date xCURRENT xYESTERDAY Ending time 235959 Time User profile KALL Name genericx XALL Remote user profile XALL Name generick XALL Relational DB Entry XALL Name generick XALL SQL Operation XALL Name XALL ALTE
21. A XYES DB LOGISTICS sasha XYES DB SALES tm1 XYES DB2ADNIN acum db DB2ADNIN xXYES DB2ADNIN ACUMSDB db2admin XYES DPROP ODED DPROP DPROP REPLICA ODED DPROP SHI DPROP DPROP 192 168 168 182 DPROP DPROP 192 168 168 4 DPROP F3 Exit F6 Add New F12 Cancel Figure 7 1 Work with Server Authentication Entries To view a specific subset of server authentication entries 1 Select one of the following subset fields m User E Server E Remote User 2 Enter the first alphanumeric characters of the entry and press Enter The list is redisplayed with results that match your query DB Gate User Manual 40 Modifying a Server Authentication Entry Modifying a Server Authentication Entry To modify a server authentication entry 1 Highlight the server authentication entry that you want to update in the Work with Server Authentication Entries screen and press 1 Select and then press Enter 2 When the Modify User Authentication Entry screen opens as shown in Figure 7 2 on page 41 update the User profile Server and Remote user ID details displayed 3 When you are done 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 Password or XNONE DB Gate restricts Server to 18 characters User and Password are restricted to 10 characters F3 Exit F12 Cancel Figure 7 2 Modify User
22. Authentication Entry 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 41 Adding a New Server Authentication Entry DB Gate User Manual 42 Test Drive This chapter 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 Enter SQL Statements screen appears This is the main screen for interactive Structured Query Language SQL for running SQL statements SQL messages also appear on this screen See Figure 8 1 on page 43 Enter SQL Statements Type SQL statement press Enter gt CONNECT TO SALES Current connection is to relational database SALES gt SELECT FIRST_NAME LAST NAME EMAIL PHONE NUMBER FROM HR EMPLOYEES WHERE FIRST NAME BETWEEN Jack AND Oliver SELECT statement run complete F3 Exit F4 Prompt F6 Insert line F9 Retrieve F10 Copy ine Fi2 Cancel Fi3 Services F24 More keys Figure 8 1 Enter SQL Statements DB Gate User Manual 43 Working with SQL Program Sources Working with SQL Program Sources To begin working with SQL Program Sources select 5 Work with SQL program sources from the main screen and press
23. ERBY Yes JavaDB Derby FIREBIRD Yes Firebird FRONTBASE Yes FrontBase 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 Print Fi2 Cancel Figure 5 2 Work with DB Drivers If the URL for the driver manufacturer has been saved within the driver s details you can open the driver s web page 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 1 Inthe 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 2 Inthe 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 DB Gate User Manual 30 Modifying a DB Driver Modifying a DB Driver To modify a DB driver do the following 1 Highlight the DB driver you want to update and press 1 Select and then press Enter The Modify DB Driver screen as shown in Figure 5 3 on page 31 opens with the driver s details Update the fields then press Enter A confirmation message appears asking if you wan
24. N US scooters EE EEES 29 Working with Database Drivers sissies cg 2acn lt ceseisacsiuigrceteapinctwasheneenuh sect sesionss canoes r iE 29 Description Jor the driver sa cecessuariatcatedeatinncesscnronesctedessentdaetsateate tect sonaiaescaadessoner 30 TWN Gay ine aA DB DVE iasence E E EEE 31 Con oo ADB DIKE erae TEA AOE EO OOO EEO OES 33 Adding a New DB MOV C Bitretetsepscese cecte tacos canes cnessin covsuten ANANN EAR ENANA 33 Delio a DEDINE eera rer ner near tenner aenee E E E ane Emme arene errr arene eer 34 Pororo MoI ses OUI e E E EE ET 34 DB Gat LOQ aciessnniisnna a aA 35 Remote USET ID uin EAEE 39 Injection of Remote User IDS cccccccccccccseeseeeeessnnseeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeseeeeeeeegs 40 Modifying a Server Authentication Entry ccccccccsssssssssssncscsseeeeeeeeeeeeeeeeeeeeeeeeeeeees 41 Adding a New Server Authentication Entry cccccccccscccseessssssssscssseeeeeeaeeeeeeeeeeeeeeeeees 41 TEST ONV O inene EENS 43 eea 9 E R ETN T setenv oa E EE EE E 43 Working with SQL Program Sources is5 ccsiwcscsssepinasanaddacseayzancnanecsawadisnsdageddanabevascevevaveceoas 44 System Configuration wie ccdesiscazenexeteccchtxncspocsiescencdebeuehecdassededeesebteecesnececs 45 STC tall Don ONS eses rena ni Ee E a 46 LOS ROIO e E A E E E E AOA 47 Setting DB Log Retention Parameters oeeeeeeensnnnnnnnnnnnssssssseenrnrssssssssseen 47 Maintenance Menu ssssaasnnunnnnnnnnnnnnnnnnnnnnnnnnnnnn
25. 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 F12 Cancel Figure 6 4 Additional Entry Information from DB Gate Log DB Gate User Manual 38 Remote User ID This chapter details how to work with server authentications The parameters and settings rely on standard IBM commands such as ADDSVRAUTE and CHGSVRAUTE 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 DB Gate User Manual 39 Injection of Remote User IDs Injection of Remote User IDs To set up auto injection of remote user IDs select 21 Work with Server Authentication from the main menu and press Enter The Work with Server Authentication Entries screen is displayed as shown in Figure 7 1 on page 40 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 xYES AU S520DDM QSECOFR xYES AV testrdb10 AV DB CRM SASH
26. R CLOSE SQL State a a w a a amp KALL Character genericx XALL SOL Error Code 6 a a a a XALL Number XERR XNOERR xXxALL SQL Statement contains xALL More F3 Exit F4 Prompt FS Refresh Fi2 Cancel Fi3 How to use this display F24 More keys Figure 6 1 Display DB Gate Log Entries Screen 1 Display DB Gate Log Entries Type choices press Enter SQL Message contains From job mame KALL Name generick ALL USOPP a e e ata ge aco mo d XALL Name genericx ALL Number 224 4 4 ee w le a KALL 000000 999999 xALL From program name XALL Name generick XALL Library aoao a s KALL Name generick XALL Number of records to process NOMAX Number xXNOMAX Output 2 2 e xK x XPRINT xOUTFILE Bottom F3 Exit F4 Prompt FS Refresh Fi2 Cancel F13 How to use this display F24 More keys Figure 6 2 Display DB Gate Log Entries Screen 2 DB Gate User Manual 36 Table 6 1 on page 37 describes the various settings you can define to filter log entries Table 6 1 Filter Log Settings Parameter Description Display last minutes Number BYTIME This Backward Glance feature lets you enter the number of minutes of recent activity 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 S
27. SPATH in iSecurity DB Gate sp properties Log level errors are always logged 3 1 No Log 2 Connects onl A AIl 1 only first FETCH logged F3 S Exit Fi2 Cancel Figure 3 3 General Definitions Screen c Set the Run mode to 2 if set to another mode and press Enter continuously usually 2 or 3 times to return to the DB Gate Main screen DB Gate User Manual 13 Mode 2 Internal Server 2 Activate the server so follows a Select 2 Activation to open that menu as shown in Figure 3 4 on page 14 Activation System DB Gate can function in server mode which requires pre activation The actual operation mode is set in System Configuration on Main Menu Internal Server Mode External Server Mode Activate Server Activation Z De activation is De activate Server on the external server Work with Active Jobs See manual for details 11 Activate Server at IPL 12 Do Not Activate Server at IPL Special 71 Recreate Data Queue Note that Inline Mode does not require pre activation Selection or command gt F3 Exit F4 Prompt F9S Retrieve Fi2 Cancel Fi3 Information Assistant F16 AS 400 main menu Figure 3 4 Activation Screen b Select 1 Activate Server DB Gate begins to operate in Internal Server Mode A message appears at the bottom of the screen indicating c Select 5 Work with Active Jobs to verify that DB Gate is working The Work with Subsystem Jobs screen appears and displays active Subs
28. activated they create an RDBDIRE Remote DB Directory Entry in the operating system Working with Directory Entries To begin working with directory entries select 1 Work with Directory Entries from the main screen The Work with Directory Entries screen opens as shown in Figure 4 1 on page 21 Directories are marked as Active or deactivated You can perform the following activities Viewing Subsets of Drivers Modifying a Directory Entry Copying an Existing Directory Entry Creating a New Directory Entry Removing a Directory Entry Activating a Directory Entry Deactivating a Directory Entry 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 SHIPMENTS Active TESTDB FS Exit F5 Refresh F6 Add new F8 Print Fi2 Cancel DB Directory Entry deleted Relational database directory entry deactivated Figure 4 1 Work with Directory Entries DB Gate User Manual 21 Viewing Subsets of Drivers Viewing Subsets of Drivers To view a specific subset of drivers type 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 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 DB Yes DB for Windows Linux DB2JDBC4 Yes DB
29. ate User Manual 17 Mode 3 External Server F3 Exit F4 Prompt F6 Insert line F9 Retrieve Enter SQL Statements SQL statement press Enter commit Commit completed disconnect pcpc External function exception SQLSTATE 38xxx Session was saved and started again STRSQL parameters were ignored Current connection is to relational database connect to pcpc Current connection is to relational database Session was saved and started again STRSQL parameters were ignored Current connection is to relational database CONNECT TO LOPC SQLCODE 438 PRODDB PCPC PRODDB FiQ Copy line Fi2 Cancel Fi3 Services F24 More keys Figure 3 11 Connecting to Database Instance 6 Open the DB Gate Server GUI by double clicking on the icon or through the start menu The options are described in DB Gate Server Commands on page 18 DB Gate Server Commands The DB Gate Connected To Database screen indicates active jobs connected to the DB Gate server See Figure 3 12 on page 18 and Table 3 1 on page 18 for a description of the relevant parameters and options DB Gate User Manual E DE Gate Server 2 0 1 Connected to 1 1 1 105 Time Job Name User Job Number 15 x Activation Group 2013 06 05 16 30 24 ILAN_MR DB 2013 06 05 15 32 17 ILAN_MR1 DB 2013 06 05 15 32 35 ILAN_MR2 DB 2013 06 05 15 32 52 ILAN_MR3 DB 2013 06 05 15 33 08 ILAN_MR4 DB 885408 885414 885417 885422 885425 2 2 2 2 2
30. data entry screens To enter data in a field type the desired text and then press Enter or Field Exit To move from one field to another without changing the contents press Tab To view options for a data field together with an explanation press F4 a To accept the data displayed on the screen and continue press Enter DB Gate User Manual 9 New Features and Functionality pa Table 2 1 on page 10 describes the function keys that may appear on data entry screens Table 2 1 Functions Keys Function Key Description Displays context sensitive help Ends the current task and returns to the screen or menu from which the task was initiated F4 Prompt Displays a list of valid options for the current field or command For certain data items a pop up selection window appears F6 Add New Creates a new record or data item Prints the current report or data item F9 Retrieve Retrieves the previously entered command F12 Cancel Returns to the previous screen or menu without updating New Features and Functionality Version 2 1 The following improvements have been made in this version A path relative to DB Gate default driver s location can be used for for specifying drivers files in addition to absolute path Internal JDBC driver for PC files Improved calculation of the length of fields m Added a link to the manual in the driver s screen Improved Excel xlsx format handling Server
31. e STRDB then select option 1 Work with Directory Entries 2 Press F6 give it a name and point it to the supplied PC_ FILE driver as shown in Figure A 1 on page 57 3 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 amp PC_FILE F4 Prompt Figure A 1 Add New Directory Entry Opt Driver Available ORACLE_SID Yes Oracle SID ORACLE_SRV Yes Oracle Service ORACLE_TNS Yes Oracle TNS PC_FILE Yes CSV Microsoft Excel Text ODF Spreadsheet XML PERVASIVE Pervasive POINTBASEL Yes PointBase Local POINTBASES Yes PointBase Server Figure A 2 Opt Driver Available The driver operates based on target file s extension m xls xlsx for MS Excel E csv for comma separated values ods for ODF spreadsheet E txt and any other extension is treated as text file DB Gate User Manual 57 Connecting A Connecting Working with the JDBC Driver Connect the entry no user password is required CONNECT TO PC Complete either of the following steps mE AnSQL COMMENT ON statement COMMENT ON QGPL MY_TABLE IS file tmp customers xls E One can also use COMMENT ON QTEMP MY_TABLEIS or COMMENT ON MY_TABLEIS NOTE 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
32. etting from one of the following options Option Description 1 No Log No data is stored 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 NOTE Modifying a Directory Entry on page 23 describes how to change a global default setting 0 Global default setting for a specific directory entry The above procedure overrides the global default setting 0 DB Gate User Manual 46 amp 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 1 Select 9 Log Retention from the DB Directory Entry System Configuration screen The DB Log Retention screen appears as shown in Figure 9 3 on page 47 and described in Table 9 1 on page 47 2 Enter the parameters as defined in the table below When you are done press Enter 3 After the confirmation message appears press Enter again to save your settings DB Log Retention 23 03 12 10 40 10 Type options press Enter Data retention period days Days 99 xNOMAX Backup program for
33. extra memory resources from the IBM 1 All memory resources and processing power are consumed on the external computer running the DB Gate server It is the user s responsibility to ensure activation of the computer and server running the external DB Gate 1 Install the DB Gate server as follows a Close all applications b Run the setup file DB Gate_setup exe The installation wizard opens ie Setup DB Gate Server O x Welcome to the DB Gate Server Setup Wizard This will install DB Gate Server 2 0 1 on your computer It is recommended that you close all other applications before continuing Click Next to continue or Cancel to exit Setup i Cancel Figure 3 6 DB Gate Server Setup Wizard Welcome Screen DB Gate User Manual 15 Mode 3 External Server c Proceed through the Wizard choosing the destination path name and shortcut settings and click Install on the Ready to Install screen See Figure 3 7 on page 16 ie Setup DB Gate Server se Ready to Install Setup is now ready to begin ingaling DB Gate Server on your computer Click Install to continue with the installation or click Back iF you want to review or change any settings Destination location C Program Files DB Gate Server Start Menu Folder DB Gate Server Additional tasks Additional icons Create a desktop icon Create a Quick Launch icon al lt Back Install Cancel Figure 3 7 Ready to Install
34. for Windows Linux F3sExit F6 Add new F8 Pr int Fi2 Cancel Figure 4 2 Work With DB Drivers DB Gate User Manual 22 Modifying a Directory Entry Modifying a Directory Entry You can update all directory entries except for the names of the Directory To modify the details of a Directory Entry 1 Type 1 in the Opt column to select it and press Enter The Modify Directory Entry screen appears with the selected directory entry filled in as shown in Figure 4 3 on page 23 Modify Directory Entry Directory Entry LOGISTICS Active Y Driver MSSQL2005_ Description This is a MS SOL Log a ge ee ee oO Q DFt 1 None 2 Basic 4 All Host or IP lt host gt 1 1 1 197 Port Sport 1433 Catalog lt catalog gt Schema lt schema gt Database lt db gt AdventureWorks 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 ad I 1 4 gt Language Support sa 4 xAUTO XNONE 4 11 for Bidi F3 Exit F4 Prompt F7 Driver page F8 Replace driver Fi2 Cancel Modify Directory Entry Part B Directory entry a e ORACLE Driver ORACLE_SID Description oo Language support eo ee ere Sees gauto xAUTO xNONE 4 11 for Bidi Keep alive interval D Minutes OQ No Keep alive statement eae SELECT x FROM DUAL F3 Exit
35. gram Selecting a client server configuration that requires additional hardware These options are cumbersome and require special settings which often prevents the user from fully utilizing standard SQL commands on the AS 400 and often include hidden infrastructure costs as well DB Gate Benefits m 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 Eliminates the need for SQLPKG even when accessing another DB2 database including one on the AS 400 m 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 or programs in languages such as RPG Cobol etc compiled using CRTSQL E Integration with the STRSQL lets you prompt to see the Column names and more and expands IBM i based DRDA functionality by enabling transparent connectivity with JDBC databases not supported by DRDA m Uses standard SQL syntax and is based upon standard OS 400 functionality so there is virtually no learning curve to get up and running 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
36. gt lt schema gt lt adl1 4 gt Language Support AUTO Default setting NONE 4 11 Based on an IBM setting for specific language support Keep alive interval Frequency the keep alive SQL statement is sent to DB to ensure connection is maintained Keep alive statement SQL statement sent to DB to ensure connection is maintained F8 Replace Driver Enables users to replace the driver configuration for this RDB entry DB Gate User Manual 24 Copying an Existing Directory Entry Copying an Existing Directory Entry You can choose to add a new 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 To copy an existing directory 1 Highlight it and select 3 Copy The Copy DB Directory Entry screen appears with the selected directory entry appearing in both the From and To Directory Entry fields as shown in Figure 4 4 on page 25 2 Modify the To Directory Entry description and press Enter twice Copy DB Directory Entry Type choices press Enter From Directory Entry MSACCESS Description To New Directory Entry es sacc COPY F3 Exit F4 Prompt Fi2 Cancel Modify data or press Enter to confirm Figure 4 4 DBG Copy Directory Entry Screen All the existing directory entry details are automatically added and the Work with Directory Entries screen is redisplayed with the newly created direct
37. he 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 a General Configuration and Maintenance DB Gate User Manual 5 System Requirements DBMENU DB Gate iSecurity System 520 Select one of the following Patent Pendingk Remote DB Definitions Activity Log 1 Work with Directory Entries 41 Display Log DB Drivers Test Drive 11 Work with DB Drivers 51 Start SOL 15 Drivers amp Licenses Folders 99 Work with SQLX program sources Auto Injection of Remote User ID General 21 Work with Server Authentication 81 System Configuration 82 Maintenance Menu Selection or command F3 Exit F4 Prompt F9 Retrieve F12 Cancel Fi3 Information Assistant F16 AS 400 main menu Figure 2 1 DB Gate Main Screen DB Gate User Manual DB Gate Benefits 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 m Using ambiguous or non standard APIs that are not defined through RDBDIRE relational Database Directory Entry and therefore make no use of an ARD pro
38. iSecurity 38 DB Gate gt User Manual Software Version 02 1 June 2013 RAZ LEE gt The iSeries Security Experts 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 FileScope Screen Password Audit Capture View Visualizer 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 Lee Security Inc Visit our web site at www razlee com
39. ion General 98 Driver Licenses Selection gt E 99 Copyright Notice Release ID 01 0 12 03 20 44DE466 520 7459 Authorization code BO1203731542 1 F3 Exit F22 Enter Authorization Code Fi2 Cancel Figure 9 1 DB Directory Entry System Configuration After you modify any of the parameters accessible from this menu the message Modify data or press Enter appears upon return to the menu DB Gate User Manual 45 General Definitions Refer to Activation on page 11 for detailed descriptions and instructions on the three Run modes To change the global settings of DB Gate 1 Select 1 General Definitions from the System Configuration screen The General Definitions screen appears as shown in Figure 9 2 on page 46 General Definitions Type options press Enter Run mode eo O ee ite ack Ose ee ee ey eo ie 1 Inline no pre activation 2 Internal server 3 External server For Run mode 2 set any password to user profile SECURITYBP to enable the initiation of the Internal Server This password does not have to be entered anywhere in the product SQL Activities are always done by the actual user authority Auto Set Java version and CLASSPATH Y Y Yes N No Java 1 5 or higher required See CLASSPATH in iSecurity DB Gate sp properties Log level errors are always logged 35 1 No Log 2 Connects only A4 AIl 1 only first FETCH logged PS Ex1rt Fi2 Cancel Figure 9 2 General Definitions Screen 2 Enter a Log level s
40. lt adl 1 4 gt F3 Exit ORACLE_SID Oracle SID oracle ojdbcsS jar oracle jdbc Oracl eDriver http wuw oracle com technol ogy tech java sql j_jdbc index h tml 1521 Label System ID Possible values DB instance XE jdbc oracle thin lt host gt lt port gt lt adl 1 gt F7 Driver page FS9 Restore Default Fi2 Cancel Figure 5 1 Modify DB Driver Full and Relative Paths DB Gate User Manual 29 Working with Database Drivers You may add any number of additional drivers or modify the existing ones To set up a database driver 1 Select 11 Work with DB Drivers from the Main Menu The Work with DB Drivers screen as shown in Figure 5 2 on page 30 with a three column list of the different types of database already entered in the system is displayed 2 You can choose to create a remote connection from this screen which includes the following details Driver Name Available Yes indicates that the driver s Driver file parameter in the Modify DB Driver screen as shown in Figure 5 3 on page 31 has been filled in with the path to the jar file on the IFS 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 i Cache DAFFODILLO Daf fodilDB Local DAFFODILSR Daffodil DB Server DB2 Yes DB2 for Windows Linux DB2JDBC4 Yes DB2 for Windows Linux D
41. m consumes the processing power of a different computer This server supports any number of jobs The functionality of DB Gate is the same regardless of the mode run No changes are required in the programs running on the IBM 1 The choice of mode selected can be made at any stage Figure 3 1 on page 11 describes the architecture of the 3 run modes Mode 1 Inline Mode 2 Internal Server Mode 3 External Server Job1 RPG COBOL RPG COBOL RPG COBOL DB Gate DB Gate External Server RPG COBOL RPG COBOL RPG COBOL DB Gate Job3 STRSQL STRSQL STRSQL DB Gate Internal Figure 3 1 Run Mode Architecture DB Gate User Manual 11 Cs Mode 2 Internal Server Mode 1 Inline Activation DB Gate runs the internal mode by default No pre activation is required Note that first SQL command within a job activates within DB Gate a process which may require a few seconds depending on available computing resources Mode 2 Internal Server DB Gate runs as server job on a dedicated subsystem This subsystem must be started before processing any SQL statement The product can be configured to start this subsystem automatically after every IPL This mode has no initial extra processing and requires fewer memory resources Before You Begin 1 Have your organization s IT Administrator ensure that all SQL jobs using DB Gate are signed off 2 Seta password for the user pr
42. ng exceptions made to the database journal Anti Virus is a dedicated iSeries specific product engineered to provide full protection to the server its file contents and resident iSeries or System i dedicated software 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 protects and secures all types of access to and from the System i 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 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 iSecurity Product Suite Product Password Assessment DB Gate User Manual Description View is a unique patent pending field level solution that hides sensitive fields and records from restricted users This innovative
43. nistrator 1 xUSE 9 xFULL F3 Exit F12 Cancel Figure 10 4 Modify Operator Screen Table 10 2 on page 52 explains the parameters on the Modify Operator Screen Table 10 2 Modify Operator Parameters Parameter VEUT Description Operator The Operator can be a user or group profile DG Gate provides one type of Operator group AUD SECAD This group has full access Read and Write to all parts of DB Gate C SAME Same as previous password when edited User of the product USE Read authority only User of the product 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 DB Gate User Manual 52 ik Check Locks Check Locks To verify if objects are locked in the system select 52 Check Locks from the Maintenance Menu The Check Locks screen appears as shown in Figure 10 5 on page 53 7 GSLCKMNU Check Locks iSecurity System 520 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 Figure 10 5 Check Locks Screen DB Gate User Manual 53 Uninstall Uninstall
44. nnnnnnnnnnnnnnnnnnnn nnmnnn nnmnnn 49 Display ola US tN OTA cece seca ca cs eien EE EE EE E 50 Work with OS 0S co eaten pte ce enetenenicteae ene case T EAEE EEEE AEO OE GEENIS 51 OTR he TGS os sessgseamnaicaa nic E AOO 53 aE eT I E E E ere erect et me tee erinten S E A tern ani eer es 54 TROUDICS MOO UNG ixissaosicacetccanendvias coseesnsvasiutetahonniseeneusetyustemnearetuneensubaeen 55 Error Messages in Server MOG vicscsorsenasaosacensateneneeotnansdsducesecis nen n Ea A UNAN A RNE Oian 56 DB Gate messages in server mode oooooeneeeeeenssssssssssssssesseerrsrrrrrrreereeeeee 56 Appendix A JDBC DIivelr cccccccceseeeeeeeeeceeeeeeeeeeeneseneneneeneeenesenesenes 57 Addin an FAU sec EE E A TS 3l CONAS e EEE E AEE OE AEREE EE E AE EEE 58 DB Gate User Manual 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
45. o New Driver WAFFODILSR F3 Exit F4 Prompt F12 Cancel Figure 5 4 Copy DB Driver Adding a New DB Driver To add a new DB driver 1 From the Work with DB Drivers screen press F6 An empty Add New DB Driver screen appears 2 After you complete the fields 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 DB driver DB Gate User Manual 33 Deleting a DB Driver Deleting a DB Driver To delete a DB driver from the list 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 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 a DIR db2 DIR derby DIR excel DIR Firebird DIR hsql db DIR h2sql DIR jtds 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 Figure
46. ofile SECURITYBP which initially has a password of NONE DB Gate User Manual 12 Mode 2 Internal Server Activation To activate DB Gate to run in Internal Server Mode 2 1 Ensure DB Gate mode is set to Internal Server 2 as follows a Go to the DB Gate Main screen and select 81 System Configuration to open that screen as shown in Figure 3 2 on page 13 DBPARMR DB Directory Entry System Configuration 26 04 13 00 31 55 Select one of the following DB Gate 1 General Definitions 9 Log Retention General 98 Driver Licenses Selection gt 99 Copyright Notice Release TDi eyes eee Be ee ee ee Se a 02 00 13 04 02 6561B 7D 520 7140 Authorization code gt gt gt o BQ1304776591 1 BERT F3 Exit F22 Enter Authorization Code Fi2 Cancel Modify data or press Enter Figure 3 2 DB Directory Entry System Configuration Screen b Select 1 General Definitions to open that screen as shown in Figure 3 3 on page 13 General Definitions Type options press Enter Run mode Shee se st ses aes Mee ee ee el sate 1 Inline no pre activation 2 Internal server 3 External server For Run mode 2 set any password to user profile SECURITYBP to enable the initiation of the Internal Server This password does not have to be entered anywhere in the product SQL Activities are always done by the actual user authority Auto Set Java version and CLASSPATH Y Y Yes N No Java 1 5 or higher required See CLAS
47. ory entry in deactivated status See Figure 4 5 on page 25 Work with DB Directory Entries Type options press Enter Subset stake 1 Select 3 Copy 4 Remove 7 Activate 8 Deactivate me co Status Directory entry Active ALEX TEST ALEX Active A150 Active AS20 Active LO Active LOPC Active MS This is a MS SQL Active MS_JTDS Active MS_ROB This is a MS SQL MSACC COPY Active MSACCESS Active MSPC is a MS SOL Active MY Active MYFED Active MYH2 F3 Exit FS Refresh F6E Add new F8 Pr int Fi2 Cancel Directory Entry copied Figure 4 5 Work with Directory Entries Screen DB Gate User Manual 25 Creating a New Directory Entry ES 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 create a new directory entry 1 Select F6 Add New to open the Add New Directory Entry screen 2 Adda Directory Entry by doing one of the following m Type the new Directory Entry alias name E Select F4 Prompt while in the Directory Entry field to display the existing Directory Entry list Select the Directory Entry by typing 1 next to it Press Enter The selected Directory Entry is added to the new Directory Entry field See Figure 4 6 on page 26 Add New Directory Entry Select Directory Entry Type options press Enter Position to 1 Select Subset Opt Directory entry ALEX TEST is a MS SOL
48. password for each CONNECT to a remote DB Provides detailed traceability logs m Uses standard SQL syntax and is based upon standard OS 400 functionality so there is virtually no learning curve m Use of the IBM Server Authentication Entries injecting user and password seamlessly when needed and eliminating the need to remember and re enter a user name and password for each CONNECT to a remote DB DB Gate User Manual 7 DB Gate Restrictions DB Gate Restrictions The following functions are not supported Database large objects BLOBs CLOBs DBCLOBs Data links User IDs longer than ten characters Passwords longer than ten characters Stored procedure result sets SQL statements longer than 32K Stored procedures with Commit on Return Scrollable cursors Multi row input Extended diagnostics RDB aliases Commitment Control NOTE When using an ARDPGIM 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 cannot be assigned NONE or NC for commitment control as shown below in Figure 2 2 on page 8 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
49. 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 group 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 1 From the Maintenance Menu select 11 Work with Operators The Work with Operators screen appears as shown in Figure 10 3 on page 51 Work with Operators Type options press Enter 1 Select 4 Delete Authority level 1 xUSE 9 xFULL System DB Adm 520 9 9 Opt User AUD SECAD CS 520 9 GS 520 9 9g 1 OS 520 1 Bottom DB DB Gate Adm Administrator F3 Exit F6 Add new F8 Print F1ii xSECADM xXAUDIT authority F12 Cancel Figure 10 3 Work with Operators To modify an operator highlight it and choose 1 Select The Modify Operator screen appears as shown in Figure 10 4 on page 52 To add a new operator press F6 DB Gate User Manual 51 Work with Operators Modify Operator Type choices press Enter Operator xAUD SECAD System 520 XALL Name Password XSAME Name XSAME XxBLANK Authorities by subject 1 xUSE 9 xFULL Product Admi
50. r Stop Server Figure 3 12 Database Connection Screen Table 3 1 Database Connection Parameters and Options Button Column Description Release Job Stops the selected job Stop Server Deactivates the DB Gate Server User who initiated the job Job Number Number of the job Activation Group Activation Group number in which the application is running 18 Changing the DB Gate Mode of Operation E Each job consist of one or more activation groups and it s important to note that DB Gate operates on the level of Activation Group Therefore the same job attributes can appear more than once but in different Activation Groups An example is provided in Table 3 2 on page 19 Table 3 2 Sample List of Connections Job Name User Job Number Activation Group QPADEV0010 DB 123456 2o QPADEV0010 DB 123456 SB Changing the DB Gate Mode of Operation The mode of operation can only be changed when no active SQL process is running in DB Gate It is recommended to change the mode of operation in conjunction with IPL To change the Run mode Notify all relevant users that you are about to stop and change the current Run mode Make sure all SQL jobs running through DB Gate are signed off If DB Gate is currently running in Server mode 2 or 3 stop the server Go the General Definitions screen 81 and set the new Run Mode Start DB Gate mE For Inline Mode 1 no further action is necessary CE SS E For Internal Ser
51. 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 protects unattended terminals and PC workstations from unauthorized use It provides adjustable terminal and user specific timeout capabilities Password provides a first tier wall of defense for users by ensuring that user passwords cannot be easily cracked 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 Overview 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 DB Gate main screen is displayed as shown below in Figure 2 1 on page 6 You can access t
52. t to modify any of the entries 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 Modify DB Driver WB2 for Windows Linux Driver file iSecurity DB Gate Drivers db2 jcc jar com ibm db2 jcc DB2Driver Driver page http www 306 ibm com software data db2 java Default port 30000 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 ad 1 1 4 gt F3 Exit F7 Driver page F9 Restore Default Fi2 Cancel Figure 5 3 Modify DB Driver The following table describes the information in the DB Driver screen Table 5 1 DB Driver Parameters and Commands Parameter Command Driver Driver s name and description It will appear in the DB Drivers list when configuring a connection Description Driver file Path to the jar file on the IFS that contains the JDBC driver for this database JDBC driver class name URL for the driver developer s web page DB Gate User Manual 31 Modifying a DB Driver E pa Table 5
53. tart Yesterday Week Start Previous Weeks Month Start Previous Months Year Start Previous Years and each day of the week Ending time fowom ooo 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 SQL Operation All default or a specific SQL operation that is used at the beginning of an SQL statement e g CONNECT CREATE DROP etc SQL State All default or a specific state SQL Error Code All default a specific error code number or name that describes the problem or entries without any error code SQL Statement contains All default or a specific SQL statement SQL Message contains All default or a specific error message that is associated with an error code All default or specific job User All default or specific user Number All default or a specific number All default or the program in the AS 400 that created this log All default or specific library of the program name Number of records to pro No Maximum amount NOMAX or a specific number cess 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 DB Gate User Manual 37 iz i Once you have defined the filter for the log press Enter to view the results The following screen
54. ver Mode 2 activate as described in Activation on page 12 E For External Server Mode 3 activate as described in Activation on page 13 DB Gate User Manual 19 Changing the DB Gate Mode of Operation Data Queues Eo E ki cS DB Gate uses a a data queue for communication when operating in server mode 2 or 3 You can recreate this data queue delete and build using option 71 in the Activation screen see Figure 3 13 on page 20 This maintenance task is useful when the data queue becomes too large This task action should be only be performed while the server is down and there are no SQL jobs employing DB Gate Activation DB Gate can function in server mode The actual operation mode is set in Internal Server Mode Activate Server De activate Server Work with Active Jobs 11 Activate Server at IPL 12 Do Not Activate Server Special 71 Recreate Data Queue System which requires pre activation System Configuration on Main Menu External Server Mode Activation Z De activation is on the external server See manual for details Note that Inline Mode does not require pre activation Selection or command gt gt F3 Exit F4 Prompt FS Retrieve Fi2 Cancel Fi3 Information Assistant F16 AS 400 main menu Figure 3 13 Activation Screen DB Gate User Manual 20 Remote DB Definitions This chapter describes how to define a remote database When definitions are
55. ystem Jobs and their status as shown in Figure 3 5 on page 14 Work with Subsystem Jobs S520 25 04 13 12 51 19 Subsystem sigh ior Meo Lg Mics hate Miet pies Wed ite ts ZDBGATE Type options press Enter 2 Change 3 Hold 4 End S Work with 6 Rel ease 7 Display message 8 Work with spooled files 13 Disconnect Opt Job User Type Function B DBMONITOR SECURITYBP AUTO ACTIVE PGM DBSRVR Parameters or command gt F3 Exit F4 Prompt F5S Refresh F9 Retrieve Fii Display schedule data Fi2 Cancel Fi 7 Top Fi8 Bottom Figure 3 5 Work with Subsystem Jobs Screen DB Gate User Manual 14 Mode 3 External Server Automatic Activation upon Startup The Internal Mode can be set to automatically activate upon startup of the IBM System Initial Program Load IPL from the Activation screen See Figure 3 4 on page 14 E To enable automatic activation select 11 Activate Server at IPL E To disable automatic activation select 12 Do Not Activate Server at IPL m You can verify automatic activation status by typing DSPJOBLOG at the command prompt Mode 3 External Server DB Gate runs as server process on a different computer That computer and the DB Gate service running on it must be activated before any SQL processing is initiated The computer running this server mode can run on Windows Unix Linux and any other Operating system that supports Java This mode has no initial extra processing and requires virtually no
Download Pdf Manuals
Related Search
DB Gate v2.1 User Manual Raz Lee