Questions and answers related to creating Db2 packages specific to the iSeries platform.

 

What grants does the Progress DataDirect for JDBC for Db2 driver on I-series require when creating packages?
What packages does the Progress DataDirect for JDBC for Db2 driver on I-series try to create the first time it connects?

The driver makes DRDA calls to communicate with the Db2 listener on iSeries.

1. What are the required grants for the JDBC driver to be able to connect to Db2 on iSeries for the first time? 
When connecting to the database for the first time or any time, the driver will do the following:
- Connect and authenticate the user
- Issue a few DRDA calls to get some database metadata
- Issue a SELECT against the QSYS2.SYSPACKAGE table to check for the existence of the SQL Packages. The user will need SELECT privilege against this table.
- If no packages are found or any packages are missing, then the driver will try to recreate the packages as per the answer to #3. By default, the driver will attempt to create the packages in the NULLID schema and then GRANT EXECUTE TO PUBLIC on them so that they can be accessed by all users in the PUBLIC group. This step can be controlled via the driver connection options GrantExecute, Grantee, PackageCollection and PackageOwner.
- Issue the command “SET CURRENT SCHEMA =” to the value specified for locationName or databaseName in the connection URL.

The standard set of Db2 grants for achieving these functions are required. Reference  the Db2 docs for the schema privileges and package privileges required:
 GRANT (schema privileges) Reference: https://www.ibm.com/docs/en/i/7.3?topic=statements-grant-schema-privileges
 GRANT (package privileges) Reference: https://www.ibm.com/docs/en/i/7.3?topic=statements-grant-package-privileges

2. What package is the driver trying to create the first time the JDBC driver tries to connect?
The driver creates 2 different packages for each isolation level supported. The DynamicSections connection option value determines how many DECLARE CURSOR commands are issued for each package. Be sure to specify a value large enough to handle the expected number of open statements that the application will have open at one time. The package naming convention is as follows:

Package Naming Convention: DDpiVRMx
This name is derived as follows:
DD -> DataDirect product
p -> product identifier
(A) = ADO .NET
(J) = JDBC
(O) = ODBC
i -> isolation level used (C,S,R,U,N)
(C)ommited Read (ANSI): Db2 CS ==> DRDA ISOLLVL CS
(S)erializable (ANSI): Db2 RR ==> DRDA ISOLLVL RR
(R)epeatable Read (ANSI): Db2 RS ==> DRDA ISOLLVL ALL
(U)ncommited Read (ANSI): Db2 UC ==> DRDA ISOLLVL CHG
(N)ot committed: Db2 NC (iSeries only) ==> DRDA ISOLLVL NC
VRM -> Three digit product release version
x -> Package suffix that differentiates between the packages:
A - Package with cursors closed at commit
B - Package with cursors held over commit

3. What steps, or what commands is the driver trying to execute when it tries to create the packages on the I-series?
The driver issues the following DRDA commands to create the packages:
BGNBND
BNDSQLSTT
ENDBND

4.  What objects on the I-series is the driver trying to access when it connects for the first time?
Refer to answer #1 above.

5.  Does the driver connect to the database and then does a create or replace the schema?
The driver does not try to create or replace any schemas. The driver expects the schema specified in the locationName or databaseName connection option to exist. During the connection handshake, the driver will issue the command “SET CURRENT SCHEMA =” the value specified for locationName or databaseName connection parameter.

6. What does the driver try to do when it connects for the first time when the Schema name is not the same name as the user profile?
The driver issues a SET CURRENT SCHEMA = command to set the schema in use to the value specified by locationName or databaseName.

7. Are there any prerequisite requirements in regard to what the default public authority needs to be set to?
There are no prerequisite requirements for the default public authority to be set to.

For information on the connection parameters mentioned above (DynamicSections , GrantExecute, Grantee, PackageCollection and PackageOwner), refer to the driver documentation.
 

References to other documentation:
DataDirect Driver Documentation, https://www.progress.com/documentation/datadirect-connectors