GENERAL STARSQL HINTS StarSQL 1.04 September 19, 1995 Copyright 1995, StarWare Connectivity Software, Inc USE OF THE SQL CATALOG QUALIFIER The SQL Catalog Qualifier specified in a data source affects only where the StarSQL driver looks for catalog information (that is, where to look for SYSTABLE when you request a list of available tables). It does not affect other SQL statements such as SELECT if the table name is not explicitly qualified (that is, "SELECT * FROM table" is used instead of "SELECT * FROM collection.table"); in this case (on the AS/400), the name of the user is used in place of collection (that is, SELECT * FROM userid.table). This affects custom SQL statements (that is, those you enter manually in an "Execute SQL"-type dialog box) or user-created applications; most commercial applications such as MS Query use fully qualified table names when preparing a query statement. USE OF THE READ-ONLY CHECKBOX: * StarSQL needs to bind SQL packages for the first connection or a connection at a new isolation level other than the default. Using the Read-Only checkbox on a data source in this situation results in the error message "Change request not valid for read-only application server." * Connecting to DB2 V2R3 and V3R1 with Read-Only checked for the StarSQL data source results in the error: "Database server does not support read-only connection." CREATING COLLECTIONS ON THE AS/400: There are three methods to create collections: using SQL/400, using StarSQL, or using Query Management/400. Using SQL/400 You can use the IBM licensed program SQL/400 (renamed DB2/400 Query Manager & SQL Development Kit in v3rX) to create an SQL collection on your AS/400 by logging on to the AS/400 with a terminal session, running the interactive SQL utility STRSQL, and issuing the command "CREATE COLLECTION ". Using StarSQL Alternatively, you can use StarSQL to create an SQL collection even if there are no SQL collections for StarSQL to bind packages to. Perform the following steps: 1. Specify an existing AS/400 library for the SQL Package Collection field and for the SQL Catalog Qualifier in the StarSQL Data Source setup dialog box. Any library should work -- it does not need to be an SQL collection, but you must have authority to create objects in the library. You can use the library QTEMP. 2. Using ODBCTest (or another interactive SQL tool on the PC), connect to the AS/400 with your datasource; StarSQL packages are created and bound in the library you specified in step 1. Note that the application must be able to ignore the warnings it receives about "object QTEMP.SYSTABLES not found"; some applications, such as Microsoft Query, treat these warnings as an error and do not allow you to progress further. To use Microsoft Access to execute the SQL statement, see "Creating Pass-Through Queries to Send Commands to SQL Databases" in Chapter 12 "Advanced Queries" of the Microsoft Access User's Guide. (Hint: To view the query property sheet mentioned in step 5, choose "Properties" from the View menu). 3. Execute the SQL statement "CREATE COLLECTION " using your StarSQL data source. This takes a while to execute. On smaller AS/400 models, the CREATE COLLECTION statement may take more than five minutes to complete. 4. Change the values of the SQL Package Collection and SQL Catalog Qualifier fields to the collection you just created. The next time you connect, new packages are bound into the SQL collection. 5. You can delete the packages in the old library by connecting with the SQL Package Collection field set to the new library (or set to the default value of your user ID if you used your user ID for the SQL collection name), preparing, and executing this SQL statement, "DROP PACKAGE .". If you use the default isolation level, two packages are created, "SWRC0000" and "SWCATPKG". If you used the library QTEMP, the files automatically disappear when the job completes. Using Query Management/400 You can also create an SQL collection on the AS/400 using Query Management/400. Perform the following steps: 1. Create a source physical file and name it QQMQRYSRC: CRTSRCPF FILE(library/QQMQRYSRC) RCDLEN(80) 2. Use SEU or PDM to add a member to the source file created in Step 1. The source type should be TXT. The name of the member will be NULL in this example. 3. The member should contain one line: CREATE COLLECTION NULLID When in SEU the member would appear as: 001.00 CREATE COLLECTION NULLID 4. Create a Query Management/400 query. Issue the CRTQMQRY command against the created member: CRTQMQRY QMQRY(library/NULL) SRCFILE(library/QQMQRYSRC) 5. Run the query to create the collection: STRQMQRY QMQRY(library/NULL) ACCESSING PHYSICAL AND LOGICAL FILES ON OS/400 StarSQL 1.03 provides improved support for physical and logical files located outside of SQL Collections. OS/400 V3R1: If you specify QSYS2 as the SQL Catalog Qualifier in the StarSQL Data Source setup dialog, you can retrieve catalog information for all files, tables, and views on the system, whether or not the file belongs to an SQL Collection. When you use the system-wide catalogs in QSYS2, the SQL Package Collection StarSQL Data Source setup) can be any library or collection where you have authority to bind SQL packages. It is possible (and recommended) that all users share the same SQL Package Collection/Library. See IsolationLevel (ODBC.INI) in Advanced Options for special considerations when accessing non-journaled physical files. You can access physical and logical files that are not in an SQL Collection (Tables and Views) with previous versions of AS/400 by explicitly specifying a physical or logical file name in an SQL collection, thus allowing off-the-shelf query applications to retrieve catalog information for the referenced file. You can also access non-collection physical files by creating a collection and then creating a view in that collection which points to the non-collection physical file. OS/400 V2Rx: To allow StarSQL to retrieve catalog information for non-collection physical or logical files on OS/400 v2r2 or v2r3, follow the procedure below. This method does not allow access to tables and views in an SQL Collection; for access to those, use StarSQL as described in the StarSQL User's Guide (specify the desired Package Collection & Catalog Qualifier in the data source; the Catalog Qualifier should be an existing SQL Collection). One time setup (using a user ID that has QSECOFR privileges): 1) Using a 5250 terminal session, create a library where the SQL packages used by StarSQL will be created. This library does not need to be a SQL collection, but should be accessible to all StarSQL users. Example: As QSECOFR, create a new library STARSQL: CRTLIB STARSQL GRTOBJAUT OBJ(STARSQL) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*USE) 2) Create a StarSQL data source, specifying Package Collection = the library created in step 1 Catalog Qualifier = QSYS You do not need to change the IsolationLevel in the data source. Example: Create a StarSQL data source: Package Collection = STARSQL Catalog Qualifier = QSYS 3) Connect to the AS/400 using this data source and a user ID that has QSECOFR privileges. This will create several SQL packages in the library specified in the data source. These packages will be named QSYS and one of SWRC0000, SWRU0000 or SWNC0000 (depending on isolation level used). Creation of the packages may take several minutes. 4) Using a 5250 terminal session, use the GRTOBJAUT command to grant *USE authority for the library and execute authority for the packages to all StarSQL users. Example: GRTOBJAUT OBJ(STARSQL) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*USE) GRTOBJAUT OBJ(STARSQL/*ALL) OBJTYPE(*ALL) USER(*PUBLIC) AUT(*USE) or using SQL statements (using SQL/400 or SQL commands sent from an ODBC-enabled application): GRANT EXECUTE ON PACKAGE STARSQL/QSYS, STARSQL/SWRC0000 TO PUBLIC Usage: Users should use a data source similar to above: Package Collection = library containing the SQL packages created above Catalog Qualifier = QSYS If an SQL collection contains no non-journaled physical files, then the collection name will not appear in the list of available libraries presented by the ODBC-enabled application. If the collection does contain any non-journaled physical files, the collection name will be shown, but only the non-journaled physical files will appear in the list of available files. KNOWN PROBLEM WITH OS/400 v2rX PHYSICAL FILE SUPPORT: If a user does not have permissions to view a library (e.g. authoritiy for PUBLIC is *EXCLUDE), that user should not be able to view the names of the files within, or even see that the library exists at all - at least that's the behavior on a 5250 session. However, the current StarSQL support for v2r3 physical files allows a user to see that the library exists and see the names of the files, though he will not be able to open any files. If there are security concerns regarding the existence or name of a library or file, we suggest that you do not use this feature; we suggest using collections (and views to physical files if necessary). No security problems exist if the setup procedure involving QSECOFR described above is not performed. RESTRICTED CATALOG VIEWS: This version of StarSQL allows users to filter the table and view names returned when retrieving a list of available tables (views, physical files) to what is available in a single AS/400 collection or library, or to a single Authorisation ID in DB2/MVS or SQL/DS. This feature is useful when using the system-wide catalog on the database server. For AS/400 v2r2 and v2r3 QSYS, AS/400 v3r1 QSYS2, DB2/MVS SYSIBM or on SQL/DS SYSTEM. The filter is specified by appending the library, AS/400 collection or an authorization-id to the SQL Catalog Qualifer in the data source setup. Examples: (AS/400 v2r2 or v2r3) "QSYS.MYLIB" (AS/400 v3r1) "QSYS2.MYLIB" With any AS/400 version, the catalog is restricted to a single collection by specifying the collection as the SQL Catalog Qualifier e.g. "MYCOLL" (DB2/MVS) "SYSIBM.MYAUTH" (SQL/DS) "SYSTEM.MYAUTH" Important note for AS/400 v2r2 and v2r3 users: To specify QSYS.MYLIB as qualifier, any existing QSYS package needs to be deleted. To do this, open a StarSQL connection to the as/400, then issue the following SQL statement "DROP PACKAGE PkgCollection.QSYS". "PkgCollection" should be replaced with the library/collection specified during data source setup. The packages can also be removed by using the DLTOBJ command. Previous versions of StarSQL will be able to use the newly created catalog package. If the driver attempts to filter the table list based on what is specified in the catalog qualifier with an old package, the error message SQLCODE -144, SQLSTATE 58003 will be returned. AS/400 v2r2 and v2r3 Isolation Levels: When QSYS is specified as catalog qualifier (or QSYS.MYLIB), the driver will bind any package used for dynamic SQL with a hardcoded IsolationLevel (*None(NC) 0 for v2r3 and *chg(ReadUncommited) 1 for v2r2) regardless of what is specified by the application or in ODBC.INI. If you wish to access physical files with other than default commitment control, bind the dynamic SQL packages using a different catalog qualifier. Examples of catalog qualifiers to use during this bind process could be QTEMP (this will give warnings but still will work) or any collection name if there are collections available. To drop dynamic packages, use DLTOBJ in the OS/400 environment or DROP PACKAGE in the SQL environment: DROP PACKAGE PkgCollection.SWNC0000 DROP PACKAGE PkgCollection.SWRU0000 DROP PACKAGE PkgCollection.SWRC0000 DROP PACKAGE PkgCollection.SWTS0000 DROP PACKAGE PkgCollection.SWRR0000 Note that if you share a package collection for use with both QSYS (physical files) and collection packages, the dynamic packages will all be bound with an effective IsolationLevel of 0 or 1, depending on os/400 version. This would be the case if the dynamic packages were bound when the catalog qualifier was specified as QSYS. If the dynamic packages (SWXX0000) were bound when any other catalog qualifier was specified, they will have their expected isolation level. (IsolationLevel can then be overridden in odbc.ini if desired). We recommend using different package collections for use with Physical files (QSYS) and "Collection use".