ALTOBJ (sql-stmt parameter size)

[sbiel]

Hello

I would like to use the stored procedure "ALTOBJ" to alter tables in a Datawarehouse and introduce this in the DDL promotion processes we have.

The sintaxis is:

ALTOBJ(exec-mode, sql-stmt, alter-id, msg)

The sql-stmt parameter is used to define the new "CREATE TABLE" and it has a limitation of VARCHAR(2048), however the table SYSTOOLS.ALTOBJ_INFO table where the sql_stmt will be located is a CLOB (2097152).

"Dimension" tables in a DW can have many descriptive columns and the 2048 size limitation is to short for us in some cases.

I was thinking about altering this stored procedure by increasing the sql-stmt parameter size, however it is not possible to alter a procedure located in the SYSPROC schema...

It seems very strange to me this limitation considering DB2 is used for DWs environments!

Is there any workaround ? am I missing something ?

Thanks a lot for your help.
Sergi

[sbiel]

Hello,

While the DB2 Information Center states VARCHAR(2048) when describing the sql-stmt param for the ALTOBJ procedure, the ROUTINENAME catalog table is showing VARCHAR(32672) which would cover our tables requirements... seems like there is a bug in the documentation.

$ db2 "select substr(PARMNAME,1,10) as PARNAME,substr(TYPENAME,1,10) as TYPENAME,LENGTH from syscat.ROUTINEPARMS where ROUTINENAME='ALTOBJ'"

PARNAME TYPENAME LENGTH
---------- ---------- -----------
EXEC_MODE VARCHAR 30
SQL_STMT VARCHAR 32672
ALTER_ID INTEGER 4
MSG VARCHAR 2048

4 record(s) selected.

Thank You