VARLEN in DDS vs. VARCHAR in DDL

[Jguty]

We are replacing all our DDS Physical Files with Tables created using DDL (SQL Create Table statments). One of the requirements is that the new Table must have the same Format Level ID, which we have been able to satisfy in almost all cases, except when the DDS source has a VARLEN field.
We are using VARCHAR in the Create Table statement to emulate the behavior of the VARLEN, but we get different Level IDs.
Any ideas on how to modernize this physical files without changing the Level IDs?


Example:

PF using VARLEN:

    A           R TABLEONE                                  
    A             KKEY           3A                         
    A             KCOLUMN1     100A         VARLEN          
    A                                       ALWNULL

Level ID = 2F38E6069DA98

Create Statement Using VARCHAR

CREATE TABLE TABLEONE (  	
  KKEY      CHAR(3)      NOT NULL DEFAULT '' ,  	
  KCOLUMN1  VARCHAR(100) DEFAULT NULL );

Level ID 2F38E6069E2A0

[efnkay]

I can see where you're going and it would be slick and easy to NOT have to recompile any programs to avoid the dreaded level-check.

There is one possibiility that might be the cause of the "new" level-id. Specifying the DEFAULT NULL on the SQL table is the only "visible" difference I can see between the DDS and the DDL created tables.

The DDS table allows nulls but has no "default" value specified. Maybe if you drop the DEFAULT NULL specification you might get the same level-id since that doesn't really match the DDS definition.

The DDS equivqalent of the DEFAULT NULL...I believe is VARLEN(1) DFT(x'00') or something similar to that.

[K.Milligan]

What level of the operating system are you seeing this on?

Our testing using the logical file conversion method described in this Redbook (http://www.redbooks.ibm.com/abstracts/sg246393.html) preserved the level id after the SQL conversion.

XCase for System i has a tool that automates & manages this SQL conversion process that preserves the level id.

[Jguty]

What level of the operating system are you seeing this on?

This test was done on V5R4.

[Guest.Visitor]

Hi Jguty,

In DB2 Every Physical file or Table having their Unique System generated Level ID’s If you are doing change in attribute of existing field of DDS then Format Level Id would be changed. Otherwise no Level Id would be changed.


Many Thanks
Ankit
Daccit Pvt Ltd