Re: Retrieving record format via SQL

I use this sql statement. I created it as a Example so I can retrieve it.

-- category: .SYS
-- description: sys_Table_Information
-- =========+=========+=========+=========+=========+
-- Create a work variable with search value Change the default and run
both
CREATE OR REPLACE VARIABLE WorkLib2.@vTmpSql01 CHAR(10) DEFAULT 'QS36F' --
Library
;
CREATE OR REPLACE VARIABLE WorkLib2.@vTmpSql02 CHAR(10) DEFAULT 'CALNDR' --
Table
;
-- #################################################
-- Show all columns in a table with keys
SELECT col.system_table_name || ' > ' || col.table_name AS Tbl_Name1030
,col.system_column_name || ' > ' || col.column_name AS
Column_Name1030
,TRIM(CAST(col.data_type AS CHAR(20))) || '(' ||
CASE
WHEN datetime_precision IS NOT NULL
THEN TRIM(CAST(datetime_precision AS CHAR(7))) || ')'
WHEN character_maximum_length IS NULL
THEN TRIM(CAST(Numeric_precision AS CHAR(7))) || ',' ||
TRIM(CAST(numeric_scale AS CHAR)) || ')'
ELSE TRIM(CAST(character_maximum_length AS CHAR(7))) || ')'
END AS Data_type2
,COALESCE((SELECT SUM(col2.storage) + 1
FROM qsys2.syscolumns col2
WHERE col2.ordinal_position < col.ordinal_position
AND col2.system_table_name = col.system_table_name
AND col2.system_table_schema = col.system_table_schema
GROUP BY col2.system_table_schema ,
col2.system_table_name) ,1) AS start_Pos
,col.ordinal_position AS Col_Pos
,kfld.dbkpos AS Key_Pos
,kfld.dbkord AS Key_order
,column_text
,column_heading
,long_comment
,col.system_table_schema || ' > ' || col.table_schema AS
Tbl_Schema1030
,col.system_table_name
,col.system_column_name
,TRIM(CAST(col.data_type AS CHAR(20))) || '(' ||
CASE
WHEN datetime_precision IS NOT NULL
THEN TRIM(CAST(datetime_precision AS CHAR(7))) || ')'
WHEN character_maximum_length IS NULL
THEN TRIM(CAST(Numeric_precision AS CHAR(7))) || ',' ||
TRIM(CAST(numeric_scale AS CHAR)) || ')'
ELSE TRIM(CAST(character_maximum_length AS CHAR(7))) || ')'
END AS Data_type2
,column_text
FROM qsys2.syscolumns col LEFT OUTER
JOIN qsys.qadbkfld kfld
ON kfld.dbklib = col.table_schema
AND kfld.dbkfil = col.table_name
AND kfld.dbkfld = col.system_column_name
WHERE table_schema = WorkLib2.@vTmpSql01
AND
table_name = WorkLib2.@vTmpSql02
ORDER BY table_schema
,TABLE_NAME
,ordinal_position -- comment this out if you want in key order
,kfld.dbkpos
;
cl:DLTSRVPGM SRVPGM(WorkLib2/@vTmpSql01)
;
cl:DLTSRVPGM SRVPGM(WorkLib2/@vTmpSql02)
;

On Fri, Jan 29, 2021 at 10:47 AM <smith5646midrange@xxxxxxxxx> wrote:

I did not find it in QADBXREF unless I overlooked it.

Guess I'll do the hard way for now. I'm out of time.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Vernon
Hamberg
Sent: Friday, January 29, 2021 11:00 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Retrieving record format via SQL

This might be worth an RFE request - I think the info might be in a table
called QADBXREF, but we don't generally have authority to it - the
SQL* and SYS* tables are often views, actually, over those QADB* files.

Trouble is, SQL-the-standard- knows nothing about formats, nor about
members
and RRNs and the like that are part of the implementation on IBM i - SQL
language really doesn't care about low-level implementation, so long as
some
layer in between converts the physical part into relational structures and
operations.

Whew! Time to go back to bed after that!

Cheers
Vern

On 1/29/2021 8:48 AM, smith5646midrange@xxxxxxxxx wrote:

Does anyone know where I can find the record format using SQL? I know
I can do DSPFD to *OUTFILE and select from it but I'd rather do it the
easy way, assuming it exists somewhere in a table already.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com