DB2 zOS reverse engineered table not right

[mreil]

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) );

[wise-coders]

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.

[mreil]

Getting the same DDL with the beta version - screenshot attached.

[wise-coders]

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.

[mreil]

Sorry I don't know. I have not used that method - my DB2 is running on an IBM mainframe - using credentials from the DBAs.

[wise-coders]

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.

[wise-coders]

We wrote to IBM, they answer they will give us details, so we are waiting. Then we can test using the docker container.

[mreil]

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

[wise-coders]

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.

[wise-coders]

Could you please check if the latest DbSchema 9.4.2 is working fine? If not, we would check this issue again.