Home > IT Answers > AS/400 > Documenting column in SQL table in QADBIFLD (AS400)
DanTheDane
2,540 pts.
 Documenting column in SQL table in QADBIFLD (AS400)
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
ASKED: March 1, 2010  10:12 AM
UPDATED: March 2, 2010  7:39 PM
RELATED QUESTIONS
Answer Wiki:
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
Last Wiki Answer Submitted:  March 2, 2010  12:09 am  by  TomLiotta   107,695 pts.
All Answer Wiki Contributors:  TomLiotta   107,695 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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

DanTheDane
 2,540 pts.
 

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

TomLiotta
 107,695 pts.