Documenting column in SQL table in QADBIFLD (AS400)

2555 pts.
Tags:
AS/400 SQL
Documentation
QADBIFLD
SQL
SQL generated files
SQL tables
When a table is created with SQL, several fields in the system cross-reference file QADBIFLD gets content (headings, defults a.o). I noticed that the QADBIFLD table contains a column named DBIREM (length 2000 bytes..). I also noticed that the old S36 IDDU actually put some documentation into this column.

Now my question: How can text-documenation be added to my SQL-table-definition, so that the text ends up in this QADBIFLD- column?.

I have created a [em]fieldref[/em]-table, and would like to add long text (documentation) to my source, before I create my application tables using SQL-SELECT on the [em]fieldref-[/em]table.

Any comments on these thoughts are welcome.



Software/Hardware used:
V6R1 on IBM Power i

Answer Wiki

Thanks. We'll let you know when a new response is added.

Note that you shouldn’t care. QADBIFLD isn’t really for your consumption and you possibly should act as if it doesn’t exist.

What you want to pay attention to are the VIEWs that are built over QADBIFLD (and over similar DDS PFs in QSYS). In your specific case, the VIEW that you’d be interested in would probably be QSYS2/SYSCOLUMNS.

The reason that might make sense to you is because (1) SYSCOLUMNS is obviously a SQL object and (2) you can affect the related QADBIFLD field through the SQL COMMENT ON statement.

Use STRSQL or other interface to run COMMENT ON statements. Reference your files and fields in the COMMENT ON statement. QADBIFLD will be updated for you automatically with your comments.

Tom

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DanTheDane
    Thanks Tom. Your answer solved the challenge. I simply added the COMMENT ON statement to my field-ref sourcefile. I discovered that long texts can be added to a column, by simply wrapping the text over several 80 char statements. Here are some sample code for those who might be interested:
    --
    --   Field reference file for 'myapp'
    --
    --
    --
    --   Version .......:  001
    --   Table name ....:  FLDREF
    --
    --   HISTORY:
    --   2010-03-01 DanTheDane  New.
    --
    -- ---------------------------------------------------------
    --  Create new table
    -- ---------------------------------------------------------
     CREATE TABLE  FLDREF
     (
    -- TOKENS
     TOKEN        INT               Generated always as Identity ,
     TOKENREF     INT               Not null with default ,
    
    -- DATE and TIMING
     Date         date              ,
     DateTime     timestamp         Not null with default ,
     CCYYMMDD     decimal(8 , 0)    Not null with default ,
     CCkode       decimal(1 , 0)    Not null with default 1,
    
    -- Row history
     EntryDate    date              ,
     EntryUser    char(10)          Not null with default ,
     UpdateDate   date              ,
     UpdateUser   char(10)          Not null with default ,
    
    -- CHARACTER FIELDS
     Boolean      char(1)           Not null with default '0',
     CustomerID   char(10)          Not null with default ,
     Remark       varchar(1024)     Not null with default ,
     Text         char(64)          Not null with default ,
     Status       char(1)           Not null with default 'A',
    
    -- NUMERIC FIELDS
     Sequence     decimal(5 , 0)    Not null with default 10,
     Amount       decimal(11, 2)    Not null with default
     );
    -- ---------------------------------------------------------
    --  Put descriptional long text onto table
    -- ---------------------------------------------------------
     COMMENT ON TABLE   MYAPPLIB.FLDREF  IS
      '*** FIELD REFERENCE FILE - myapp ***';
    
    -- ---------------------------------------------------------
    --  Put short text onto member
    -- ---------------------------------------------------------
     LABEL ON TABLE   MYAPPLIB.FLDREF  IS
       '*** FIELD REFERENCE FILE - myapp ***';
    
    -- ---------------------------------------------------------
    --  Put coloumn descriptional text onto table
    -- ---------------------------------------------------------
     LABEL ON COLUMN   MYAPPLIB.FLDREF
    --                ....:....|....:....|....:....|....:....|....:....|....:....|..
     (
     TOKEN        IS '                                        TOKEN',
     EntryDate    IS 'Record              Creation            date',
     EntryUser    IS 'Record              Created             by'
     );
    
     LABEL ON COLUMN   FLDREF
     (
     TOKEN        TEXT IS 'TOKEN',
     ENTRYDATE    TEXT IS 'Record creation date',
     ENTRYUSER    TEXT IS 'Record created by',
     UPDATEUSER   TEXT IS 'Record update date',
     UPDATEDATE   TEXT IS 'Record updated by'
     );
    
     COMMENT ON COLUMN   FLDREF
     (
     EntryDate    IS 'Date of record creation. Date is set automatically by the syst
    em.',
     EntryUser    IS 'User-ID for person who entered this record. Rows may exist whe
    re special values starting with an asterisk (''*'') are used, fx ''*CONVERT'' an
    d ''*IMPORT''.',
     UpdateDate   IS 'Date of latest record update. Date is set automatically by the
    system (trigger-pgm). Field is null at record entry to the table.',
     STATUS       IS 'Record status field. Defaults to ''A'' (Active) when record is
     created. May be set to ''I'' (Inactive), ''D'' (Deleted) or ''*'' (may be erase
    d.'
     );
    
    -- END -----------------------------------------------------
    
    To execute the sourcecode use cmd RUNSQLSTM. DanF
    2,555 pointsBadges:
    report
  • TomLiotta
    Note that you can run COMMENT ON for DDS PFs, too. Of course, if the PF is ever recreated, the COMMENT ON must also be re-run. I haven't tested CHGPF, but I'd sure expect that COMMENT ON would still need to be re-run. I wouldn't recommend the above as a standard practice for DDS! Conversion to SQL DDL would be better. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following