[System]
We are planning to transfer data of an old database to a new one using DataStage jobs.
Both of the databases are created on DB2 for z/OS.
We have two environments. One is a production environment, the other is a rehearsal environment which is newly created to simulate the production environment.
Tables of the old database on the production environment were created with DB2 for z/OS V4.1.
Tables of the old database on the rehearsal environment were created with DB2 for z/OS V10.
Tables of the new database on both of the environmet were created with DB2 for z/OS V10.
Some of the tables are partitioned tables.
DB2 connector stage is used to access the tables.
Tables of the old database == Read ==> DB2 Conncetor Stage of DataStage jobs == Load ==> Tables of the new database
[Differences in Job Logs]
Job logs have messages such like below.
One has DECIMAL fucntion, but the other does not have DECIMAL function in the SQL select sentence.
LIMITKEY value in the DECIMAL function for the partition range does not have DOT at the end of the value, like '20999999999', but the other has DOT like '20999999999.'
Logs of the jobs run on the production environment:
SELECT AAA FROM TTT WHERE (BBB > '20999999999.') AND (BBB <= '21999999999.') FOR FETCH ONLY
Logs of the jobs run on the rehearsal environment:
SELECT AAA FROM TTT WHERE (BBB > DECIMAL('20999999999', 11, 0)) AND (BBB <= DECIMAL('21999999999', 11, 0)) FOR FETCH ONLY
[Questions & Requests]
1. Why does the difference (with/without DECIMAL) in the job logs above happen ?
I asked the reason in PMR R2B-IPS-TS002012136, but the datastage support team did not answer it because the difference do not actully cause any problems yet, and the support team persists that PMR can handle only a defect report.
Please also refer to PMR R2B-IPS-TS002012136 in details.
2. We would like DB2 Connector to use the DECIMAL function in both of our environments for a decimal LIMITKEY value.
[DDLs]
The following is part of the DDL to create the table space and the table.
--- DDL for the production environment:-----
CREATE TABLESPACE TSxxx IN DByyy
NUMPARTS 10
(PART 01
USING STOGROUP SGzzz
PRIQTY 0279360 SECQTY 0028080 ERASE NO
FREEPAGE 020 PCTFREE 020 ,
PART 02
: )
:
CREATE TABLE TTT
(BBB DECIMAL (0011,0) NOT NULL WITH DEFAULT, <=== Partition key is defined as decimal type.
: )
IN DByyy.TSxxx
DATA CAPTURE CHANGES
WITH RESTRICT ON DROP ;
--- DDL for the rehearsal environment: ---
CREATE TABLESPACE TSaaa IN DBbbb
NUMPARTS 10
(PART 01
USING STOGROUP SGccc
PRIQTY 0279360 SECQTY 0028080 ERASE NO
FREEPAGE 020 PCTFREE 020 ,
PART 02
: )
SEGSIZE 0
:
CREATE TABLE TTT
(BBB DECIMAL (0011,0) NOT NULL WITH DEFAULT, <=== Partition key is defined as decimal type.
: )
IN DBbbb.TSaaa
The following is part of the DDL to create the index.
--- DDL for the production environment:-----
CREATE TYPE 2 UNIQUE INDEX Innnnnnn
ON SIKNT
(SHRKY_NO ASC ,
: )
CLUSTER
(PART 01 VALUES(20999999999.) <=====** There is a dot at the end of the VALUES
USING STOGROUP SGCIS007
PRIQTY 050400 SECQTY 05040 ERASE NO
FREEPAGE 020 PCTFREE 20 ,
PART 02
: )
--- DDL for the rehearsal environment:---
CREATE TYPE 2 UNIQUE INDEX Innnnnnn
ON SIKNT
(SHRKY_NO ASC ,
: )
CLUSTER
(PART 01 VALUES(20999999999) <=====** There is NO dot at the end of the VALUES
USING STOGROUP SGccc
PRIQTY 050400 SECQTY 05040 ERASE NO
FREEPAGE 020 PCTFREE 20 ,
PART 02
: )
Thank you for submitting this idea.
As of now, and with the details provided in this item, we're not able to explore further this item.
We would need more details, on the enhancements you expect and the product version you're using.
Please open a support request for further analysis on https://www.ibm.com/support/home/