Re: SQL - Creating a duplicate of an existing column

I recall using DESCRIBE against a prepared statement to describe its columns, so I am confident that a column of a table can be described from a prepared statement of:
SELECT #{column_name} FROM #{table_name}

There is a DESCRIBE TABLE statement, in addition to the more general DESCRIBE statement. They would often be used _in_ a stored procedure, but I do not know about how either might be used to describe a stored procedure.

There is also the SQL catalog VIEW SYSCOLUMNS, for which IIRC at least the ALLOCATE of a VARxxxx type is missing; the base table for that VIEW is QADBIFLD which is available to *ALLOBJ users, but any one of its logical files such as QADBILFI can be accessed instead by any user. There is also the CL command DSPFFD to an output file. Finally there is the API QDBRTVFD for the format with the Qdb_Qddfmt Format Header and the fields with the Qdb_Qddffld Field Header to get the field information to without the output file or catalog approach. I have not used QUSLFLD API, but that should give information on the database file fields as well.

Regards, Chuck

Pete Helgren wrote:

Looking at the documentation, it looks like DESCRIBE is used
for retrieving information about stored procedures, not
tables or columns.
At least all the examples seem to be using stored procedures.

Carel Teijgeler wrote:

DESCRIBE may do the trick, I think you need the SQLDA for
that, but is according to me required in some aspects, as
you want ageneric solution..

On 16-12-2009 at 14:34 Pete Helgren wrote:

I have a Java application that uses JDBC to the DB2 for i database on my i. It is using a RENAME COLUMN function that
isn't yet supported by DB2 (someday....). In any case, since
I can't change the application I'd like to build a stored
procedure to handle the function. I propose to do something
like:

"ALTER TABLE #{table_name} ADD COLUMN #{new_column_name}"
"Update #{table_name} set #{new_column_name} = #{column_name}"
"Alter table #{table_name} drop column #{column_name}"

The kicker is, in order for this to be generic and work, I
have to retrieve the attributes of the column I want to
rename and copy and I don't know how to do that. Does
anybody have an SQL method for retrieving attributes of a
column so that they can be used to create a column with the
same attributes?