Sample Code for Generating a List of Tables to Rebuild

The following sample SQL code can be used to generate a list of the target tables with LONG VARCHAR columns that need to be re-created before applying ADDITIVE schema changes.

Run this code against the staging schema after the additive changes are applied to the Siebel staging database. If no tables match the selection criteria in the code, then you do not have to rebuild any target tables.

--
-- CREATE A DROP LIST FOR LONGVARCHAR TABLES
--
SELECT
SUBSTR(
CONCAT(
CONCAT(' DROP TABLESPACE ' ,
CONCAT(STRIP(T.DBNAME) ,
CONCAT('.',
STRIP(T.TSNAME)
)
)
)
, ' /'
)
, 1, 36) AS STATEMENT
,CONCAT(' -- ', T.NAME) AS COMMENT
FROM SYSIBM.SYSTABLES AS T
WHERE
T.CREATOR = STAGING_TABLE_OWNER --<<< STAGING TABLE OWNER AND
T.NAME NOT LIKE 'EIM_%'
AND
EXISTS (SELECT 1 -- longvarchar table
FROM SYSIBM.SYSCOLUMNS C
WHERE C.TBCREATOR = T.CREATOR
AND C.TBNAME = T.NAME
AND C.COLTYPE = 'VARCHAR'
AND C.LENGTH=16350)
AND
EXISTS (SELECT 1 -- has been altered
FROM SYSIBM.SYSCOLUMNS K
WHERE K.TBCREATOR = T.CREATOR
AND K.TBNAME = T.NAME
AND K.ALTEREDTS != T.CREATEDTS)
ORDER BY 1
;

whereSTAGING_TABLE_OWNER is the staging database table owner in your environment.

The following is an example of a list of tables generated by running the query in the sample code:

DROP TABLESPACE D0000005.H1000000 / -- S_ORG_EXT 
DROP TABLESPACE D0000006.H2000000 / -- S_NOTE_FUL_REQ
DROP TABLESPACE D0000007.H3000000 / -- S_NOTE_MDF