Binding Packages
StarAdmin binds StarSQL driver packages on the host database and provides the option of granting EXECUTE or USE authority on the packages created. Users must be able to execute these packages to take advantage of the StarSQL driver.
This topic discusses the DB2 authority you need to bind packages, the Package Settings options and Grant Options that you can set before you execute the bind command, the names of dynamic SQL packages, and deleting a package. You also can override the settings of the DSN that is used to connect to the database for the bind operation by clicking the Advanced button of the main StarAdmin window. See the Introduction topic for general information, such as why and when you need to bind packages.
The general procedure for binding packages is summarized below.
- To bind packages on the DB2 host, you must first connect to a database. After you connect to the host the bottom of the StarAdmin window shows the database name, the name of the package collection, the database host type and database version.
- From the Command menu, select Bind to create and bind a complete set of packages. A summary window displays the results of the bind operation. Note that you can use the values shown for the keywords in the summary to configure a datasource for the StarSQL ODBC driver to use, or the jdbc URL that is returned in the summary to connect to a host using the StarSQL for Java driver.
- Review the results and click Finished to return to the main StarAdmin window.
DB2 Authority Requirements for StarAdmin
For StarAdmin to successfully bind SQL packages, the StarAdmin user must have BINDADD authority to create packages on the host database. For StarAdmin to successfully grant EXECUTE or USE authority, the StarAdmin user must have GRANT authority on the database. For DB2 for i the equivalent authority required by the StarAdmin user is the ability to create a library ("create collection" authority) and to bind is to have *USE authority for a given library ("package collection"). Executing packages is equivalent to a user, or *PUBLIC, having *EXECUTE permission on the "SQLPKG" objects.
Additional privileges, which vary according to the host type, are required to delete a package. Refer to the DB2 documentation for your host system for more information about the specific authority privileges that are required.
Package Settings
The Package Settings options let you control what catalog is used for the bind operation, and the level of detail that is returned from the bind operation.
Suppress Bind Warnings
The Suppress Bind Warnings option allows you to display only errors, or errors and warnings, that are reported for the bind operation. Warnings can be informative to a database administrator in some situations, but usually it is sufficient to display only bind errors. Enable (checkmark) the Suppress Bind Warnings option to display only errors from the bind operation. Disable (no checkmark) the option to display both warnings and errors.
Use Default Catalog Option
The catalog package that the StarSQL driver uses depends on the value of the Catalog Schema setting for the StarSQL data source (DSN). For each host system, there is a corresponding default catalog package which matches the system catalog name. If the proper Catalog Schema name is set in the DSN, StarSQL creates the necessary catalog package automatically. If you enable the Use Default Catalog option in StarAdmin and bind packages, StarAdmin binds the default catalog package specified by the host system.
The following table describes the default catalog package that corresponds to the Catalog Schema for different host systems.
| Host | Catalog Schema | Default Catalog |
| DB2 for z/OS | SYSIBM | SYSIBM |
| DB2 for i (DB2 for iSeries, DB2/400) | QSYS2 | QSYS2 |
| DB2 for Linux, UNIX and Windows (DB2 LUW) | SYSCAT | SYSCAT |
If you would like to use a custom catalog name, uncheck the Use Default Catalog box and enter the name of the catalog in the available field before you execute the bind command.
Grant Options
Once the packages are created, users must be able to execute the packages to take advantage of the StarSQL driver. StarAdmin can automatically grant EXECUTE or USE authority to the packages to the PUBLIC, to a specific user, or to neither.
Set the desired grant option before you execute the bind command. The default and recommended option is Grant Package Access to Public. If you would like to grant access to a specific user, click the radio button Grant to Specific User and enter the user ID in the text field. If you do not want StarAdmin to grant access to any user, select the Don’t Grant Package Access option before binding packages. Note that package access will need to be granted manually on the host database before a StarSQL user will be able access the packages and run database queries.
If you rebind existing packages, new packages will inherit the permissions of the old packages no matter which Grant Option is in effect during the rebind. For example, if you bind a new set of packages and grant EXECUTE authority to user DB2USER1, and then bind packages again in the same collection and grant EXECUTE authority to user DB2USER2, both DB2USER1 and DB2USER2 will have EXECUTE authority to the packages.
Binding All Packages
During the initial connection to the database, StarAdmin creates one package if it does not already exist on the host. If the package already exists, StarAdmin verifies that the User ID is authorized to use the package. StarAdmin does not overwrite existing packages on the initial connection.
After a StarAdmin user successfully connects to a database, StarAdmin displays a list of all SQL packages that are stored in the specified Package Collection. The default package collection is STARSQL.
To bind all packages, select the Bind command from the Command menu.
StarAdmin displays a Summary dialog at the end of the bind operation, to report whether the operation was successful or encountered errors. If all packages are bound successfully, StarAdmin automatically grants EXECUTE or USE authority to PUBLIC or to a specific user, if either option is enabled at the time StarAdmin binds the packages. If the Summary shows that errors were encountered, correct the errors and perform the bind operation again (refer to the Troubleshooting topic for tips on correcting bind errors).
Names of Dynamic SQL Packages
Catalog packages can have predetermined names (e.g., SYSIBM, QSYS2, SYSCAT, SYSTEM) or arbitrary names the StarAdmin user gives to a catalog, as discussed in Use Default Catalog Option.
Dynamic SQL packages have predetermined names. The following tables list the names of the dynamic SQL packages that are created by StarSQL or by StarAdmin for DB2 for z/OS, DB2 for i , and DB2 LUW . The packages correspond to various isolation levels and held cursors settings.
DB2 for z/OS and OS/390
| Catalog Schema | Catalog Package Name |
|---|---|
| SYSIBM or SYSIBM.AUTHID | SYSIBM |
For a catalog view, the package name is user-specified.
| Isolation Level | Held Cursors=No | Held Cursors=Yes |
|---|---|---|
| Read Committed | SWRC0000 | SWRC1000 |
| Repeatable Read | SWRR0000 | SWRR1000 |
| Read Uncommitted | SWRU0000 | SWRU1000 |
| Serializable | SWTS0000 | SWTS1000 |
| Isolation Level | Held Cursors=No | Held Cursors=Yes |
|---|---|---|
| Read Committed | SWRC2000 | SWRC3000 |
| Repeatable Read | SWRR2000 | SWRR3000 |
| Read Uncommitted | SWRU2000 | SWRU3000 |
| Serializable | SWTS2000 | SWTS3000 |
| Isolation Level | Held Cursors=No | Held Cursors=Yes |
|---|---|---|
| Read Committed | SWRC4000 | SWRC5000 |
| Repeatable Read | SWRR4000 | SWRR5000 |
| Read Uncommitted | SWRU4000 | SWRU5000 |
| Serializable | SWTS4000 | SWTS5000 |
| Isolation Level | Held Cursors=No | Held Cursors=Yes |
|---|---|---|
| Read Committed | SWRC6000 | SWRC7000 |
| Repeatable Read | SWRR6000 | SWRR7000 |
| Read Uncommitted | SWRU6000 | SWRU7000 |
| Serializable | SWTS6000 | SWTS7000 |
DB2 for i
| Catalog Schema | Catalog Package Name |
|---|---|
| QSYS2 or QSYS2.LIBRARY | QSYS2 |
For a catalog view, the package name is user-specified.
| Isolation Level | Held Cursors=Off | Held Cursors=On |
|---|---|---|
| None | SWNC0000 | SQNC1000 |
| Read Committed | SWRC0000 | SWRC1000 |
| Repeatable Read | SWRR0000 | SWRR1000 |
| Read Uncommitted | SWRU0000 | SWRU1000 |
| Serializable | SWTS0000 | SWTS1000 |
DB2 LUW
For DB2 for Linux, UNIX, and Windows, StarAdmin creates the catalog package SYSCAT if the Use Default Catalog check box is selected. For a catalog view, the package name is user-specified.
Isolation Level |
Held Cursors=Off |
Held Cursors=On |
| Read Committed | SWRC0000 | SWRC1000 |
| Repeatable Read | SWRR0000 | SWRR1000 |
| Read Uncommitted | SWRU0000 | SWRU1000 |
| Serializable | SWTS0000 | SWTS1000 |
Deleting a Package
If you performed an upgrade of the StarSQL or DB2 software, you may need to explicitly drop and re-create packages. Typically it is better practice to drop the packages directly on the host and then run StarAdmin to create and bind new packages, as StarAdmin has a package in use when it is connected to DB2.