We help IT Professionals succeed at work.

How to retrieve FieldLength

How can I retrieve the length from a field on an iSeries machine.

I use a odbc to connect and then SQL statements (ADO Query)
Comment
Watch Question

Author

Commented:
What I mean with fieldlength is That I want to check the "definition" of the field. Not the length of the data in the field. (I need to know what is the max length of a string that I can put in a field)
In MSSQL, you can use something like:
select object_name(id)as TableName,* from syscolumns where object_name(id)='<YourTableName>'

You could of course add a JOIN to systypes to get the name of the type...

Jerry

Author

Commented:
That is in Mssql.

I think I have solved my problem temporary by using checking TADOQuery.Fields[ix].Size;
But would be great to do it with an SQL statement against the DB2
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Hi pucko,

Perhaps the easiest is to query the view SYSIBM.COLUMNS.

Depending on the datatype and exactly what you want, you could want the data from CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, or several other fields.



Good Luck,
Kent

SELECT *
FROM sysibm.columns
WHERE table_name = 'mytable'
  AND table_schema = 'myschema'
  AND column_name = 'mycolumn';

Open in new window

Theo KouwenhovenApplication Consultant
CERTIFIED EXPERT
Commented:
Hi Pucko,

From the AS/400 you can also query or sql the table QADBXREF in library QSYS, there you find all fields with specs from all files from all libraries.

Just take a look.

Regards,
Murph

Explore More ContentExplore courses, solutions, and other research materials related to this topic.