Download DB Gate User Manual - Raz-Lee
Transcript
DB-Gate TM The Remote Database Access Component of iSecurity User Manual Version 1.5 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-Lee Security Inc. Visit our website at http://www.razlee.com . Record your Product Authorization Authorization Code Here: Computer Model: Serial Number: Authorization Code Table of Contents About This Manual ....................................................................................................... 1 Product Documentation Overview............................................................................................ 1 Printed Materials ............................................................................................................... 1 Online Help ....................................................................................................................... 1 Typography Conventions......................................................................................................... 1 Other iSecurity Products.......................................................................................................... 2 Chapter 1: Introducing DB-Gate .................................................................................. 4 System Requirements ............................................................................................................. 4 Starting DB-Gate ..................................................................................................................... 4 Why You Need DB-Gate.......................................................................................................... 5 Typical Remote Access Limitations ................................................................................... 5 DB-Gate Benefits..................................................................................................................... 6 DB-Gate Restrictions............................................................................................................... 6 Native OS/400 Text Based User Interface................................................................................ 7 Menus............................................................................................................................... 7 Data Entry Screens ........................................................................................................... 7 New Features and Functionality............................................................................................... 8 Version 1.5........................................................................................................................ 8 Chapter 2: Remote DB Definitions .............................................................................. 9 Work with Directory Entries...................................................................................................... 9 Modifying a Directory Entry.............................................................................................. 10 Copying an Existing Directory Entry................................................................................. 12 Creating a New Directory Entry ....................................................................................... 12 Chapter 3: DB Drivers ................................................................................................ 13 Working with Database Drivers.............................................................................................. 13 Modifying a DB Driver ..................................................................................................... 14 Copying a DB Driver........................................................................................................ 16 Adding a New DB Driver.................................................................................................. 17 Deleting a DB Driver........................................................................................................ 17 Drivers & Licenses Folders .................................................................................................... 18 Chapter 4: DB-Gate Log ............................................................................................. 19 Chapter 5: Remote User ID ........................................................................................ 23 Injection of Remote User IDs ................................................................................................. 23 Modifying a Server Authentication Entry .......................................................................... 24 Adding a New Server Authentication Entry ...................................................................... 24 Chapter 6: Test Drive ................................................................................................. 25 Starting SQL.......................................................................................................................... 25 Working with SQL Program Sources...................................................................................... 26 DB Gate User Manual Version 1.5 – Table of Contents Chapter 7: System Configuration .............................................................................. 27 General Definitions ................................................................................................................ 28 Log Retention........................................................................................................................ 29 Setting DB Log Retention Parameters ............................................................................. 29 Chapter 8: Maintenance Menu ................................................................................... 31 Display DB-Gate Definitions................................................................................................... 31 Work with Operators.............................................................................................................. 33 Check Locks.......................................................................................................................... 35 Add DB-Gate to Java CLASSPATH ....................................................................................... 36 Journal Definition Files .......................................................................................................... 37 Adding a Journal ............................................................................................................. 37 Removing Journal ........................................................................................................... 38 Displaying a Journal ........................................................................................................ 39 Uninstall ................................................................................................................................ 39 Appendix A: JDBC Driver for Excel, CSV, ODS and Text Files ............................... 40 Adding an Entry..................................................................................................................... 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. 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 Bold. References to chapters or sections are written in Italic. OS/400 commands and system messages are written in Bold Italic. Key combinations are separated by a dash, for example: Shift-Tab. 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 i 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-thebox” security. Other iSecurity products include: 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. 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 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 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 i 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. DB Gate User Manual Version 1.5 – About This Manual 2 Firewall 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 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 unattended 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 1 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: Remote DB Definitions DB Drivers Activity Log General Configuration and Maintenance B Gate User Manual Version 1.5 – Chapter 1: Introducing DB-Gate 4 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 (i.e. SQL). Typical Remote Access Limitations To successfully access remote databases, companies have selected products based on either of the following limited methods: 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 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 AS/400 and often include hidden infrastructure costs. DB Gate User Manual Version 1.5 – Chapter 1: Introducing DB-Gate 5 DB-Gate Benefits “Opens” the entire non-DB2 spectrum of databases Greatly expands programmer’s capabilities when working with non-DB2 databases 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) 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...) Integration with the STRSQL lets you prompt to see the Table names, Column names, and more Expands IBM i-based DRDA functionality by enabling transparent connectivity with JDBC databases not supported by DRDA 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 password for each CONNECT to a remote DB Provides detailed traceability logs. 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 DB Gate User Manual Version 1.5 – Chapter 1: Introducing DB-Gate 6 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. Native OS 400 Text Based User Interface DB-Gate is designed to be a user-friendly product. The user interface follows standard System i 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 Convenient option prompts 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 Search and filtering with generic text support. The following describes the different 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 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 F1 - Help Display context-sensitive help F3 - Exit End the current 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 F6 - Add New Create a new record or data item F8 - Print Print the current report or data item F9 - Retrieve Retrieve the previously-entered command F12 - Cancel Return to the previous screen or menu without updating New Features and Functionality Version 1.5 Support added for NVARCHAR field type Enhancements made to embedded SQL in RPG 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), 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 – 8 2 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 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 Screen DB Gate User Manual Version 1.5 – Chapter 2: Remote DB Definitions 10 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 Directory Entry Name of the directory Active Y (yes) or N (no) Driver The driver you associated with the new entry Description 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) Host or IP The address used to access the remote database Port 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 Database Database name Additional parameters: SID in this example Up to four different parameters, based on the selected driver. URL Provided by the DB driver provider. May include parameters: <host><ip><user><pwd><catalog><port> <schema><adl1-4> Language Support *AUTO = Default setting *NONE 4-11 = Based on an IBM setting for specific language support. For more details, see: F8 Replace Driver = Enables users to replace the driver configuration for this RDB entry DB Gate User Manual Version 1.5 – Chapter 2: Remote DB Definitions 11 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. 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 is the same as the Modify Directory Entry Screen above. Add New Directory Entry DB Gate User Manual Version 1.5 – Chapter 2: Remote DB Definitions 12 3 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: Driver Name 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 Description for the driver. 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: Cache MS JDBC Daffodil MS SQL 2005 DB2 for Windows/Linux MySQL JavaDB/Derby Oracle Firebird Pervasive Frontbase PointBase HSQLDB PostgreSQL Informix Sybase SQL Anywhere Mimer Sqlite MS Excel 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: 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. 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. Highlight 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 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. Driver file Path to the jar file on the IFS that contains the JDBC driver for this database. Class JDBC driver class name. Driver page URL for the driver developer’s webpage. Default port The default port to use when setting up a connection. Additional Parameters You may define up to four parameters which are specific 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 15 Additional Commands F4 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. F9 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. 2. 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 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. 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. 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 & Licenses Folders To view and update a connection to a remote database, select 15. Drivers & 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 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 4 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 Screen B Gate User Manual Version 1.5 – Chapter 4: DB-Gate Log 19 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 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, 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 Ending time 00:00:00 format 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 20 Parameter Description 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 From job name All (default) or specific job User All (default) or specific user Number All (default) or a specific number From program name All (default) or the program in the AS/400 that created this log Library All (default) or specific library of the program name Number of records to process No Maximum amount (NOMAX) or a specific number Output 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 To view further details on the output, select F10. Entire Entry. The following screen appears. Additional Entry Information from DB-Gate Log DB Gate User Manual Version 1.5 – Chapter 4: DB-Gate Log 22 5 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 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. 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 following Work with Server Authentication Entries screen is displayed. 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, press Enter. After the confirmation message appears, press Enter again to save your settings. 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 6 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 Screen The Enter SQL Statements display is the main screen for 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 Screen DB Gate User Manual Version 1.5 – Chapter 6: Test Drive 26 7 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 begin system configuration, Select 81. System Configuration from the main screen and press Enter. The following DB Directory Entry System Configuration screen appears. 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 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 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: 1. Select 9. Log Retention from the DB Directory Entry System Configuration screen. The following DB Log Retention screen appears. 2. Enter the parameters as defined in the table below. 3. When you are done, press Enter. After the confirmation message appears, press Enter again to save your settings. 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. Library Enter the name of the library where it is located. DB Gate User Manual Version 1.5 – Chapter 7: System Configuration 30 8 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. Maintenance Menu Screen Display DB-Gate Definitions To print the definitions that you entered for DB-Gate, do the following: 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 Screen Parameter Description Report Type *ALL = All of the following options *DBENTRY = The definitions for the Directory Entries *DBDRVR = The definitions for the DB Drivers From item Character value *All *Start = From the beginning of all values To item Character value *Only *Last Format *List = Short form *Details = Full form Output * = 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 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. From the Maintenance Menu, select 11. Work with Operators. 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 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 Operator group: *AUD#SECAD. This group has full access (Read and Write) to all parts of DB-Gate. System *ALL Name Password Name = Password *SAME = 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 35 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 36 Journal Definition Files A journal file records changes made to definitions in the data library. Adding 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. Create Journal – Confirmation Screen DB Gate User Manual Version 1.5 – Chapter 8: Maintenance Menu 37 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. End Journal - Confirmation Screen DB Gate User Manual Version 1.5 – Chapter 8: Maintenance Menu 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 Uninstall 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 39 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 values, 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. 2. Press F6, give it a name and point it to the supplied PC_FILE driver. Press Enter. 3. Set log level and language support if needed and confirm. The entry is activated and available for use. The supplied driver in option #11 (no editing is needed here): 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: xls for MS Excel csv for comma-separated values ods for ODF spreadsheet 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 ON' 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, when issuing the SELECT statement the QTEMP library must be specified: SELECT * FROM QTEMP.MY_TABLE 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. 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. 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