Dynamically Setting the Schema Doesn't Work during Fetch

[KrishnaBee]

Hi,

I am working on a utility to extract configuration tables across different DB2 schema. There's a bit of programming required since the configuration table data are stored in a fixed length column.

The utility has 2 input param --- table name and the target table schema.

We are using DB2 for iSeries.

The schemas are:
DEV - Development environment
UTT - Unit Testing
QAT - QA Testing

I created the procedure in the DEV environment. Here's the structure. The "Set Schema" statement doesn't work and the configuration extract is always from the DEV schema.

create procedure DEV.extractTbl (
  in tblName char(8),
  in tblSchema char(8)
)

Begin Atomic

  set SCHEMA tblSchema;  

  declare cursor (select * from configTbl where tbl = tblName;
  fetch;
  process/format fetch results
  write fetch results in a temporary table;

end;

Appreciate your help.

Thanks,
Krisha

[zmbd]

Take a look at the documentation here and see if it helps.
There are several conditions that must be meet before this will work and too many to go into detail.
IBM DB2 9.7 - SET SCHEMA statement
Specifically:

The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register. It is not under transaction control. If the package is bound with the DYNAMICRULES BIND option, this statement does not affect the qualifier used for unqualified database object references.

[KrishnaBee]

thanks zmbd. I'll into the documentation. I'll reply back on with my solution. Cheers!