IBM Support

Generating field reference detail on CREATE TABLE AS



Generating field reference detail on CREATE TABLE AS


You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Database Engineer (DBE) Enhancements > Generating field reference detail on CREATE TABLE AS

Impact Analysis tools use reference field information to identify tables and programs that need to be modified.  Before this enhancement, SQL tables did not propagate reference information. 

  • CREATE TABLE AS is enhanced to store the originating column and table as the reference information in the file object.
  • When using LIKE to copy columns from another table, REFFLD information will be copied for each column that has a REFFLD in the original table.
  • When using AS to define a new column, any column that directly references a table or view (not used in an expression) will have a REFFLD defined that refers to that column.
    • A simple CAST will also generate REFFLD information (i.e. CAST (PARTNAME as varchar(50)) )

create table slr/base (a int, b int, c int); 
create table slr/as1 as (select * from slr/base) with no data;   
create table slr/like1 like slr/as1; 

Improved field reference example


[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
11 April 2021