Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Dear Brenda

From your post, I see that you are ACTUALLY talking about an SQL table. BXX_WAA360 is the schema name in Db2 for i (it is also called a Library object in IBM i). An SQL table in DB2 for i is also called a physical file object in IBM i jargon (back when SQL was not yet available in DB2/400) but both are the same database entity.   

>>>> Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables.   <<<<

You have the names wrong. I guess you are referring to DB metadata or data dictionary by another name (in IBM i, it is called catalog tables and views) and the full list is here: IBM i catalog tables and views at  https://www.ibm.com/docs/en/i/7.5?topic=views-i-catalog-tables    

So, you should query SYSCOLUMNS in QSYS2 library like this sample: 

SELECT table_name, table_schema, column_name, data_type, length FROM  qsys2.syscolumns WHERE table_schema = 'BXX_WAA360' AND table_name = '....' AND column_name = '.....' 

I also attach a basic article about DB metadata in Db2 for i for you digestion.  

Let us know when you have more questions. 

------------------------------
Satid S

Original Message:
Sent: Wed April 10, 2024 04:20 PM
From: brenda grossnickle
Subject: Finding the defined row length for a file (not a table) using SQL

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Thanks for the attachment and links. They are very useful. The three views that I listed in my original post all work at my shop. Maybe they are custom views or archive views. I have been using them for over 10 years. My files are not returned with the qsys2.syscolumns or qsys2.systables views. Below Guy suggested using qsys2.sysfiles and this did return my files. One of the columns of sysfiles is SQL_OBJECT_TYPE and it was NULL. Tested sysfiles with the most common objects that I query and they all returned SQL_OBJECT_TYPE = TABLE and they returned table information when using systables. But these files are not SQL tables and my usual queries did return any results.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

Original Message:
Sent: Wed April 10, 2024 09:28 PM
From: Satid S
Subject: Finding the defined row length for a file (not a table) using SQL

Dear Brenda

From your post, I see that you are ACTUALLY talking about an SQL table. BXX_WAA360 is the schema name in Db2 for i (it is also called a Library object in IBM i). An SQL table in DB2 for i is also called a physical file object in IBM i jargon (back when SQL was not yet available in DB2/400) but both are the same database entity.   

>>>> Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables.   <<<<

You have the names wrong. I guess you are referring to DB metadata or data dictionary by another name (in IBM i, it is called catalog tables and views) and the full list is here: IBM i catalog tables and views at  https://www.ibm.com/docs/en/i/7.5?topic=views-i-catalog-tables    

So, you should query SYSCOLUMNS in QSYS2 library like this sample: 

SELECT table_name, table_schema, column_name, data_type, length FROM  qsys2.syscolumns WHERE table_schema = 'BXX_WAA360' AND table_name = '....' AND column_name = '.....' 

I also attach a basic article about DB metadata in Db2 for i for you digestion.  

Let us know when you have more questions. 

------------------------------
Satid S

Original Message:
Sent: Wed April 10, 2024 04:20 PM
From: brenda grossnickle
Subject: Finding the defined row length for a file (not a table) using SQL

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Dear Brenda

Your response jogs my faint memory that there are actually 3 sets of DB catalog views in IBM i and further Google search reveals that you are using what is called ANS and ISO Catalog Views which I never had to deal with before:  Db2 for i catalog views at https://www.ibm.com/docs/en/i/7.5?topic=reference-db2-i-catalog-views  

qsys2.tables, sysibm.tables, or sysibm.sqltables belong to ANS and ISO Catalog Views set and here is its full list:  https://www.ibm.com/docs/en/i/7.5?topic=views-ans-iso-catalog      

But QSYS2.SYSFILES catalog view belongs to IBM i Catalog Tables and Views and if you read its description from the first URL above, you will see this :  The SYSFILES view contains one row for every Db2 for i database file, including native files. This view includes attributes not returned by QSYS2.SYSTABLES.

The "native file" is physical or logical file created by native IBM i language (called DDS - Data Definition Specification) that existed on the first day of CPF (the predecessor of OS/400 and IBM i) about 40 years ago before SQL was available.

Logical file in native IBM i jargon is the same as SQL view or index. When you create SQL table, view, or index, they are seen from IBM i native interface as physical or logical files. The DB metadata of physical and logical files are maintained in DB2 for i catalog just like SQL tables, views, and indexes.  These physical and logical files can be used by SQL interface in/to IBM i in a normal way.   Hope this answers your question "How is it that some files are tables and some are not? ".   

------------------------------
Satid S

Original Message:
Sent: Thu April 11, 2024 09:49 AM
From: brenda grossnickle
Subject: Finding the defined row length for a file (not a table) using SQL

Thanks for the attachment and links. They are very useful. The three views that I listed in my original post all work at my shop. Maybe they are custom views or archive views. I have been using them for over 10 years. My files are not returned with the qsys2.syscolumns or qsys2.systables views. Below Guy suggested using qsys2.sysfiles and this did return my files. One of the columns of sysfiles is SQL_OBJECT_TYPE and it was NULL. Tested sysfiles with the most common objects that I query and they all returned SQL_OBJECT_TYPE = TABLE and they returned table information when using systables. But these files are not SQL tables and my usual queries did return any results.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

Original Message:
Sent: Wed April 10, 2024 09:28 PM
From: Satid S
Subject: Finding the defined row length for a file (not a table) using SQL

Dear Brenda

From your post, I see that you are ACTUALLY talking about an SQL table. BXX_WAA360 is the schema name in Db2 for i (it is also called a Library object in IBM i). An SQL table in DB2 for i is also called a physical file object in IBM i jargon (back when SQL was not yet available in DB2/400) but both are the same database entity.   

>>>> Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables.   <<<<

You have the names wrong. I guess you are referring to DB metadata or data dictionary by another name (in IBM i, it is called catalog tables and views) and the full list is here: IBM i catalog tables and views at  https://www.ibm.com/docs/en/i/7.5?topic=views-i-catalog-tables    

So, you should query SYSCOLUMNS in QSYS2 library like this sample: 

SELECT table_name, table_schema, column_name, data_type, length FROM  qsys2.syscolumns WHERE table_schema = 'BXX_WAA360' AND table_name = '....' AND column_name = '.....' 

I also attach a basic article about DB metadata in Db2 for i for you digestion.  

Let us know when you have more questions. 

------------------------------
Satid S

Original Message:
Sent: Wed April 10, 2024 04:20 PM
From: brenda grossnickle
Subject: Finding the defined row length for a file (not a table) using SQL

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

hello Brenda,

look at qsys2.sysfiles et more specifically to the column maximum_record_length, I think you will find what you are looking for. 

best regards, Guy

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))

That is exactly what I needed. Thanks! When running sys2.sysfiles and my files are returned they have SQL_OBJECT_TYPE as NULL whereas all the other objects that i query have SQL_OBJECT_TYPE as 'TABLE'. How is it that some files are tables and some are not? what is it called when the file is not a table (ifs, native, data stream, logical ...)? FYI - These files come into the IBMi from a third party via some type of FTP. 

hello Brenda,

look at qsys2.sysfiles et more specifically to the column maximum_record_length, I think you will find what you are looking for. 

best regards, Guy

Hope that i am in the correct forum. 

Disclaimer, I am an MS SQL developer and want to use SQL to find the defined row length of a file. The file is not a SQL object. I can use TABLE(QSYS2.OBJECT_STATISTICS ... to query the file. Cannot use qsys2.tables, sysibm.tables, or sysibm.sqltables. I can return the data and then use a LEN on the returned data, but want to see how the file row length is defined. Is it possible using SQL? Also, what is this type of file called - one that is not also setup as a table?

this is the only ways that i have found to "query" the library or the files in it

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('BXX_WAA360','ALL'))

SELECT * FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/QSYS.LIB/BXX_WAA360.LIB'))