dbschema
dbschema copied to clipboard
wise-coders
DB2 zOS reverse engineered table not right
Using version 9.1.3 build 221114. I pointed DbSchema at my DB2 on zOS database to reverse engineer a schema. All of the tables and columns were retrieved, but when I do a "Build SQL..Create Statement" command, the resulting DDL is not right/valid - below is an example. Does the product support DB2 on zOS?
DB2zOS table on MainFrame DDL from DbSchema: CREATE TABLE "DBA$DB1P".REM_ACTN_RSN_CTGRY ( ACTN_RSN_CTGRY_CD CHAR NOT NULL DEFAULT N , ACTN_RSN_DESC VARCHAR DEFAULT Y , ACTN_RSN_EFCTV_TS TIMESTMP DEFAULT Y , ACTN_RSN_TRMNTN_TS TIMESTMP DEFAULT 1 , DEACTVTN_OPRTR_ID CHAR DEFAULT Y , DEACTVTN_TS TIMESTMP DEFAULT 1 , PSTG_OPRTR_ID CHAR DEFAULT Y , PSTG_TS TIMESTMP DEFAULT Y , CONSTRAINT "DBA$DB1P".PK_REM_ACTN_RSN_CTGRY PRIMARY KEY ( ACTN_RSN_CTGRY_CD ) );
Using a different tool, I get the correct DDL for this table: DB2zOS table on MainFrame DDL From DBeaver CREATE TABLE "DBA$DB1P".REM_ACTN_RSN_CTGRY ( ACTN_RSN_CTGRY_CD CHAR(2) NOT NULL, ACTN_RSN_DESC VARCHAR(160), ACTN_RSN_EFCTV_TS TIMESTAMP, ACTN_RSN_TRMNTN_TS TIMESTAMP DEFAULT '4000-12-31-00.00.00.000000', PSTG_TS TIMESTAMP, DEACTVTN_TS TIMESTAMP DEFAULT '4000-12-31-00.00.00.000000', PSTG_OPRTR_ID CHAR(8), DEACTVTN_OPRTR_ID CHAR(8), CONSTRAINT REM_ACTN_RSN_CTGRY_PK PRIMARY KEY (ACTN_RSN_CTGRY_CD) );
We tested today using a DB2 database (not ZOS), and for us the reverse engineer and generate script are working fine. Could you please try to test it using this beta version: https://dbschema.com/beta.php The version number is the same, the build number is higher.
We tried installing DB2ZOS as here. We got the container running. https://www.ibm.com/docs/en/db2-for-zos/12?topic=ictsf-deploying-text-search-db2-zos-as-docker-images Do you know which should be the username and database? Starting the container did print a token, which I assume is the password.
Sorry I don't know. I have not used that method - my DB2 is running on an IBM mainframe - using credentials from the DBAs.
We are searching a solution for the container issue.
Can you please run this query and send me the results? SELECT TBNAME, NAME , COLTYPE, LENGTH, SCALE, DEFAULT, NULLS, REMARKS FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR=?
Please replace the ? with the schema name, in quotes.
We wrote to IBM, they answer they will give us details, so we are waiting. Then we can test using the docker container.
There are over 33k rows, so I am providing the rows specific to our sample table REM_ACTN_RSN_CTGRY:
TBNAME NAME COLTYPE LENGTH SCALE DEFAULT NULLS REM_ACTN_RSN_CTGRY ACTN_RSN_CTGRY_CD CHAR 2 0 N N REM_ACTN_RSN_CTGRY ACTN_RSN_DESC VARCHAR 160 0 Y Y REM_ACTN_RSN_CTGRY ACTN_RSN_EFCTV_TS TIMESTMP 10 6 Y Y REM_ACTN_RSN_CTGRY ACTN_RSN_TRMNTN_TS TIMESTMP 10 6 1 Y REM_ACTN_RSN_CTGRY DEACTVTN_OPRTR_ID CHAR 8 0 Y Y REM_ACTN_RSN_CTGRY DEACTVTN_TS TIMESTMP 10 6 1 Y REM_ACTN_RSN_CTGRY PSTG_OPRTR_ID CHAR 8 0 Y Y REM_ACTN_RSN_CTGRY PSTG_TS TIMESTMP 10 6 Y Y
Please excuse the delays. Our main issue is that we don't have a test installation in our lab for Db2ZOS. If you have some test database that we can access through a tunnel, please let me know. It would help us to connect, test, and fix this issue. In this case please contact us using Help / Report a bug from DbSchema.
Could you please check if the latest DbSchema 9.4.2 is working fine? If not, we would check this issue again.