GENERAL STARSQL HINTS StarSQL 1.05 December 15, 1995 Copyright 1995, StarWare Connectivity Software, Inc. USING 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. USING 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." MULTI-MEMBER SUPPORT ON OS/400: You can use StarSQL to access multi-member files on OS/400 v3r1 from a custom application by performing the following steps: 1. Enter the OVRDBF (Override with Database File) command in an SQL statement: CALL QSYS.QCMDEXC ('OVRDBF FILE(MYFILE) TOFILE(LIB/MYFILE) MBR(MEMBER) OVRSCOPE(*JOB)', 0000000066.00000) where "66" in the second parameter is the number of characters in the command between the single quotes, including the spaces. You must use the correct number of zeros. For example: CALL QSYS.QCMDEXC('OVRDBF FILE(QCSRC) TOFILE(DAVIDLIB/QCSRC) MBR(SRCSVR) OVRSCOPE(*JOB)', 0000000068.00000) where "QCSRC" is a multi-member file. 2. Enter the command: SELECT * FROM DAVIDLIB.QCSRC Results are returned from the member SRCSVR (which is not the first member of the file). USING STORED PROCEDURES ON THE AS/400: StarSQL 1.05 allows ODBC-enabled applications to evoke AS/400 programs (stored procedures) using the SQL CALL statement. The implementation supports input and output parameters (through the program argument list). Any OS/400 program written in the supported high-level languages (see SQL Programming SC41-3611 for supported languages) can be used as a stored procedure. To make use of this new feature, you must manually delete any existing SWRC0000 and SWNC0000 packages. A new package will be bound the next time you establish a connection using StarSQL. Limitations: Do not use parentheses in data source names; if you do so, you will be unable to delete the names without having to manually edit ODBC.INI or the registry. Result sets cannot be returned from the AS/400 stored procedures. Results from the stored procedure can be returned using output parameters. Unless all parameters to the stored procedure are passed using parameter markers, all parameters will be treated as SQL_PARAM_INPUT_ONLY. When all parameters are passed as parameter markers (?), the call type of the reference to the stored procedure will be SIMPLE CALL WITHOUT NULLS. Using this calling convention, all parameters must be SQL_PARAM_INPUT_OUTPUT and NULLS are not allowed. See DB2/400 for host language (ILE-C, RPG, CL) restrictions on data types supported as arguments. No catalog information is available to display existing stored procedures (programs). 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 v1.03 and later 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 steps in the procedure described below. StarSQL pre-v1.05: In StarSQL pre-v1.05, the following method did not allow you to access SQL-created tables and views in an SQL Collection or in a library; if you are using StarSQL pre-v1.05 and want access to SQL-created tables and views, 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). StarSQL v1.05 and later: Using the following method will allow you to see SQL-created tables and views in non- collection libraries. Index information will not be available when you use QSYS as the catalog qualifier. NOTE: To make use of this new feature in StarSQLv1.05, you must manually delete any existing QSYS packages. A new package will be bound the next time you establish a connection using StarSQL. 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".