Creating the extended attribute system tables in DB2 databases
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
- Sybase DirectConnect interface
Creating the extended attribute system tables
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.
Using the DB2SYSPB.SQL script
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
on the PowerBuilder CD-ROM. This directory contains server-side
installation components and is not installed with PowerBuilder on
your computer.
You can access the DB2SYSPB.SQL script
directly from your computer’s CD-ROM drive or
you can copy 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 the product CD in your computer’s CD-ROM drive and
the drive letter is Z.
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. -
Insert the PowerBuilder CD-ROM into the computer’s
CD-ROM drive. -
Use any text editor to modify Z:ServerDB2SYSPB.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 online Help. - Change all instances of PBOwner
-
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.
This part describes how to establish, manage, and troubleshoot
database connections.