USE or SET Schema

[Chico]

I have a tool (Enterprise Architect) that creates SQL scripts based upon the design we have built.

I am attempting to run these SQL scripts using OPS Navigator, and I'm running into a dilema. To avoid me having to explicitly define the schema (library) on each and every TABLE reference, I want to include the SQL statement: USE SCHEMA chicotest;

Unfortunately this command is not supported.

Does anyone know what syntax I can include to avoid me having to manually go in and update every TABLE reference?

Here's a very small sample:

-- --------------------------------------------------
-- Generated by Enterprise Architect Version 6.1.790
-- Created On : Wednesday, 17 May, 2006
-- --------------------------------------------------

USE SCHEMA chicotest; -- If this SQL statement worked I wouldn't have to qualify my schema (library) on every table definition.

-- Drop Tables
DROP TABLE jobJob
;



-- Create Tables
CREATE TABLE jobJob (
jobId bigint NOT NULL, -- This column is the unique identifier for a job.
name varchar(50), -- This column is the name of the job.
description varchar(50), -- This column is a brief description of the job.
status char(1), -- This column indicates the state of the job; running, paused, completed, or error.
startDateTime timestamp, -- This column indicates the date/time the job started.
userId bigint, -- This column is the unique identifier of the user that started the job.
environmentId integer, -- This column is the unique identifier of the environment where the job is currently running.
programName varchar(50), -- This column is the name of the program that runs the job.
functionId bigint -- This column is the unique identifier of the function that will be used to process the job.
)
;
COMMENT ON TABLE jobJob
IS 'This table contains all of the jobs in the system.'
;
COMMENT ON COLUMN jobJob.jobId
IS 'This column is the unique identifier for a job.'
;
COMMENT ON COLUMN jobJob.name
IS 'This column is the name of the job.'
;
COMMENT ON COLUMN jobJob.description
IS 'This column is a brief description of the job.'
;
COMMENT ON COLUMN jobJob.status
IS 'This column indicates the state of the job; running, paused, completed, or error.'
;
COMMENT ON COLUMN jobJob.startDateTime
IS 'This column indicates the date/time the job started.'
;
COMMENT ON COLUMN jobJob.userId
IS 'This column is the unique identifier of the user that started the job.'
;
COMMENT ON COLUMN jobJob.environmentId
IS 'This column is the unique identifier of the environment where the job is currently running.'
;
COMMENT ON COLUMN jobJob.programName
IS 'This column is the name of the program that runs the job.'
;
COMMENT ON COLUMN jobJob.functionId
IS 'This column is the unique identifier of the function that will be used to process the job.'
;


-- Create Primary Key Constraints
ALTER TABLE jobJob ADD CONSTRAINT PK_jobJob
PRIMARY KEY (jobId);


-- Create Indexes
ALTER TABLE jobJob
ADD CONSTRAINT UQ_jobJob_jobId UNIQUE (jobId);

-- End of script --

If there's no way around this then I will go in and do the qualifying, but if anyone has any suggestions, I would appreciate the help.

[littlepd]

Re: USE or SET Schema

From the IBM InfoCenter...

SET SCHEMA

The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

No authorization is required to execute this statement.

Syntax

.-CURRENT-. .-=-.
>>-SET--+-+---------+--SCHEMA-+--+---+-------------------------->
'-CURRENT_SCHEMA------'

>----+-schema-name-----+---------------------------------------><
+-USER------------+
+-host-variable---+
+-string-constant-+
'-DEFAULT---------'


Description

The value of the CURRENT SCHEMA special register is replaced by the value specified.

schema-name
Identifies a schema. No validation that the schema exists is made at the time the CURRENT SCHEMA is set.
USER
This value is the USER special register.
host-variable
A host variable which contains a schema name.

The host variable:

* Must be a character-string variable.
* Must not be followed by an indicator variable.
* Must include a schema that is left justified and must conform to the rules for forming an ordinary or delimited identifier.
* Must be padded on the right with blanks.
* Must not be the null value.
* Must not be the keyword USER.

string-constant
A character constant with a schema name.
DEFAULT
The CURRENT SCHEMA is set to its initial value. The initial value for SQL naming is USER. The initial value for system naming is *LIBL.

Notes

CURRENT SCHEMA: The value of the CURRENT SCHEMA special register is used as the qualifier for all unqualified names in all dynamic SQL statements except in programs where the DYNDFTCOL has been specified. If DYNDFTCOL is specified in a program, its schema name is used instead of the CURRENT SCHEMA schema name.

For SQL naming, the initial value of the CURRENT SCHEMA special register is equivalent to USER. For system naming, the initial value of the CURRENT SCHEMA special register is '*LIBL'.

Setting the CURRENT SCHEMA special register does not effect the CURRENT PATH special register. Hence, the CURRENT SCHEMA will not be included in the SQL path and functions, procedures and distinct type resolution may not find these objects. To include the current schema value in the SQL path, whenever the SET SCHEMA statement is issued, also issue the SET PATH statement including the schema name from the SET SCHEMA statement.

Transaction considerations: The SET SCHEMA statement is not a commitable operation. ROLLBACK has no effect on the CURRENT SCHEMA.

Syntax alternatives: CURRENT SQLID is accepted as a synonym for CURRENT SCHEMA and the effect of a SET CURRENT SQLID statement will be identical to that of a SET CURRENT SCHEMA statement. No other effects, such as statement authorization changes, will occur.

SET SCHEMA is equivalent to calling the QSQCHGDC API.

Examples

Example 1: The following statement sets the CURRENT SCHEMA special register.

SET SCHEMA = RICK

Example 2: The following example retrieves the current value of the CURRENT SCHEMA special register into the host variable called CURSCHEMA.

EXEC SQL VALUES(CURRENT SCHEMA) INTO :CURSCHEMA

The value would be RICK, set by the previous example.

[Chico]

Re: USE or SET Schema

When I attempt to run that code I get:

> SET SCHEMA chicotest

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword SCHEMA not expected. Valid tokens: PATH OPTION RESULT CURRENT CONNECTION TRANSACTION. Cause . . . . . : The keyword SCHEMA was not expected here. A syntax error was detected at keyword SCHEMA. The partial list of valid tokens is PATH OPTION RESULT CURRENT CONNECTION TRANSACTION. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

[littlepd]

Re: USE or SET Schema

Try...

SET CURRENT SCHEMA chicotest;

[Chico]

Re: USE or SET Schema

> SET CURRENT SCHEMA chicotest

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword SCHEMA not expected. Valid tokens: PATH FUNCTION. Cause . . . . . : The keyword SCHEMA was not expected here. A syntax error was detected at keyword SCHEMA. The partial list of valid tokens is PATH FUNCTION. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

[littlepd]

Re: USE or SET Schema

DOH!!!

SET SCHEMA = chicotest

[Chico]

Re: USE or SET Schema

> SET SCHEMA = chicotest

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword SCHEMA not expected. Valid tokens: PATH OPTION RESULT CURRENT CONNECTION TRANSACTION. Cause . . . . . : The keyword SCHEMA was not expected here. A syntax error was detected at keyword SCHEMA. The partial list of valid tokens is PATH OPTION RESULT CURRENT CONNECTION TRANSACTION. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

[littlepd]

Re: USE or SET Schema

Sorry, I've reached full capacity. It works from VB using OLE DB. I don't know why Ops Nav doesn't like it.

[Chico]

Re: USE or SET Schema

Thanks for the efforts. I'll keep looking...

[Chico]

Re: USE or SET Schema

The solution to this problem is found within the Run SQL Scripts utility. What you need to do is before running the script, go to the Connection Tab. Select JDBC Setup. Click Server tab and enter the SQL default library.

The utility uses this setting value when adding the tables.

[littlepd]

Re: USE or SET Schema

Thanks for the update. I'll be posting that info in my company's internal knowledgebase on Monday.