This section describes how PowerBuilder creates the extended attribute system tables in your DB2 database to store extended attribute information. It then explains how to use the DB2SYSPB.SQL script to create the extended attribute system tables outside PowerBuilder.
You can use the DB2SYSPB.SQL script if you are connecting to the IBM DB2 family of databases through any of the following database interfaces:
-
ODBC interface
-
SAP DirectConnect interface
When you create or modify a table in PowerBuilder, the information you provide is stored in five system tables in your database. These system tables contain extended attribute information such as the text to use for labels and column headings, validation rules, display formats, and edit styles. (These system tables are different from the system tables provided by your DB2 database.)
By default, the extended attribute system tables are created automatically the first time a user connects to the database using PowerBuilder.
When you use the DirectConnect interface
When you use the DirectConnect interface, the extended attribute system tables are not created automatically. You must run the DB2SYSPB.SQL script to create the system tables as described in Using the DB2SYSPB.SQL script.
To ensure that the extended attribute system tables are created with the proper access rights:
-
Make sure the first person to connect to the database with PowerBuilder has sufficient authority to create tables and grant permissions to PUBLIC.
This means that the first person to connect to the database should log in as the database owner, database administrator, system user, system administrator, or system owner, as specified by your DBMS.
Why do this
If you are a system administrator at a DB2 site, you might prefer to create the extended attribute system tables outside PowerBuilder for two reasons:
-
The first user to connect to the DB2 database using PowerBuilder might not have the proper authority to create tables.
-
When PowerBuilder creates the extended attribute system tables, it places them in the default tablespace. This might not be appropriate for your needs.
When using the DirectConnect interface
You must create the extended attribute system tables outside PowerBuilder if you are using the DirectConnect interface. You need to decide which database and tablespace should store the system tables. You might also want to grant update privileges only to specific developers or groups.
What you do
To create the extended attribute system tables, you run the DB2SYSPB.SQL script outside PowerBuilder. This script contains SQL commands that create and initialize the system tables with the table owner and tablespace you specify.
Where to find DB2SYSPB.SQL
The DB2SYSPB.SQL script is in the Server directory in the PowerBuilder setup program. This directory contains server-side installation components and is not installed with PowerBuilder on your computer.
You can access the DB2SYSPB.SQL script by copying it to your computer.
Use the following procedure from the database server to create the extended attribute system tables in a DB2 database outside PowerBuilder. This procedure assumes you are accessing the DB2SYSPB.SQL script from d:\server.
To create the extended attribute system tables in a DB2 database outside PowerBuilder:
-
Log in to the database server or gateway as the system administrator.
-
Use any text editor to modify d:\server\DB2SYSPB.SQL for your environment. You can do any of the following:
-
Change all instances of PBOwner to another name.
Specifying SYSIBM is prohibited
You cannot specify SYSIBM as the table owner. This is prohibited by DB2.
-
Change all instances of database.tablespace to the appropriate value.
-
Add appropriate SQL statement delimiters for the tool you are using to run the script.
-
Remove comments and blank lines if necessary.
PBCatalogOwner
If you changed PBOwner to another name in the DB2SYSPB.SQL script, you must specify the new owner name as the value for the PBCatalogOwner DBParm parameter in your database profile. For instructions, see PBCatalogOwner in the section called “PBCatalogOwner” in Connection Reference.
-
-
Save any changes you made to the DB2SYSPB.SQL script.
-
Execute the DB2SYSPB.SQL script from the database server or gateway using the SQL tool of your choice.
