IBM Support

Sample SQL script for generating tables and triggers on i5/OS

Troubleshooting


Problem

The JDBC J2EE Connector architecture (JCA) resource adapter ships a sample script for DB2 called scripts_db2.sql. For iUDB on i5/OS, the SQL syntax in the DB2 sample script does not function as it is written.

Cause

iUDB requires fully qualified schema SQL in the form SCHEMA.TABLE. The current script does not contain qualified schema.

Resolving The Problem

You must modify scripts_db2.sql for use on i5/OS to include the fully qualified schema name to the table and trigger names of the table and trigger scripts.

For example, this is a sample table creation script from scripts_db2.sql:

CREATE TABLE customer


(
pkey VARCHAR(10) NOT NULL PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(20),
ccode VARCHAR(10)
);

For use on i5/OS, the script must be modified as follows:

CREATE TABLE <schema_name>.customer
(
pkey VARCHAR(10) NOT NULL PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(20),
ccode VARCHAR(10)
);

where you replace <schema_name> with the fully qualified schema name.

[{"Product":{"code":"SSMKUK","label":"WebSphere Adapters Family"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Adapter for JDBC","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.0;6.0.0.1;6.0.0.2;6.0.2","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
15 June 2018

UID

swg21248010