--############################################## HOW TO RECREATE TABLE DDL FROM DBC ######################################################## -- * * * THE HEAD PART SELECT ( HBHR + CAST(0.01 AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( 'CREATE ' ||CASE WHEN CheckOpt='Y' THEN ' MULTISET ' ELSE ' SET ' END ||'TABLE ' || TRIM(T.DATABASENAME) ||'.'||TRIM(T.TABLENAME)||' ' || CASE WHEN ProtectionType='Y' THEN ',FALLBACK' ELSE ',NO FALLBACK' END AS VARCHAR(3000) ) AS THESTRING FROM DBC.TABLES T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME UNION SELECT ( HBHR + CAST(0.02 AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( CASE WHEN SUBSTR(TRIM(JournalFlag),1,1)='N' THEN ',NO BEFORE JOURNAL' WHEN JournalFlag='S' THEN 'BEFORE JOURNAL' ELSE ',DUAL BEFORE JOURNAL' END AS VARCHAR(3000) ) AS THESTRING FROM DBC.TABLES T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON 1=1 WHERE T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME UNION SELECT ( HBHR + CAST(0.03 AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( CASE WHEN SUBSTR(TRIM(JournalFlag),2,1)='N' THEN ',NO AFTER JOURNAL' WHEN JournalFlag='S' THEN 'AFTER JOURNAL' ELSE ',DUAL AFTER JOURNAL' END AS VARCHAR(3000) ) AS THESTRING FROM DBC.TABLES T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME UNION SELECT ( HBHR + CAST(0.04 AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( ',CHECKSUM=DEFAULT ' AS VARCHAR(3000) ) AS THESTRING FROM DBC.TABLES T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME UNION -- * * * THE COLUMNS PART * * * SELECT ( HBHR + CAST(0.05 +ROWN/10000.0 AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( CASE WHEN ROWN=1 THEN '(' ELSE '' END ||THECOLUMNLINE||(CASE WHEN ROWN=MXROWN THEN ' )' ELSE ',' END) AS VARCHAR(3000) ) AS THESTRING FROM ( SELECT DATABASENAME ,TableName ,CAST( ROWN AS DECIMAL(38,18)) AS ROWN ,CAST ( ColumnName || ' ' || ColumnType || CharLength || CharacterSet || UpperCaseFlag || Nullable || DefaultValue || CompressValueList AS VARCHAR(3000) ) AS THECOLUMNLINE , MAX(ROWN) OVER (PARTITION BY DATABASENAME, TABLENAME ORDER BY 1 ) AS MXROWN FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY DATABASENAME, Tablename ORDER BY ColumnId ASC) AS ROWN ,C.DATABASENAME ,C.Tablename ,trim(C.Columnname) as Columnname ,CASE WHEN ColumnType = 'AT' THEN 'TIME' || '(' || trim(DecimalFractionalDigits) || ')' WHEN ColumnType = 'BF' THEN 'BYTE' WHEN ColumnType = 'BO' THEN 'BLOB' WHEN ColumnType = 'BV' THEN 'VARBYTE' || '(' || trim(ColumnLength) || ')' WHEN ColumnType = 'CF' THEN 'CHAR' WHEN ColumnType = 'CO' THEN 'CLOB' WHEN ColumnType = 'CV' THEN 'VARCHAR' WHEN ColumnType = 'D' THEN 'DECIMAL' || '(' || DecimalTotalDigits || ',' || DecimalFractionalDigits || ')' WHEN ColumnType = 'DA' THEN 'DATE FORMAT ''' || trim(ColumnFormat) || '''' WHEN ColumnType = 'DH' THEN 'INTERVAL DAY TO HOUR' WHEN ColumnType = 'DM' THEN 'INTERVAL DAY TO MINUTE' WHEN ColumnType = 'DS' THEN 'INTERVAL DAY TO SECOND' WHEN ColumnType = 'DY' THEN 'INTERVAL DAY' WHEN ColumnType = 'F' THEN 'FLOAT FORMAT ''' || trim(ColumnFormat) || '''' WHEN ColumnType = 'HM' THEN 'INTERVAL HOUR TO MINUTE' WHEN ColumnType = 'HR' THEN 'INTERVAL HOUR' WHEN ColumnType = 'HS' THEN 'INTERVAL HOUR TO SECOND' WHEN ColumnType = 'I1' THEN 'BYTEINT' WHEN ColumnType = 'I2' THEN 'SMALLINT' WHEN ColumnType = 'I8' THEN 'BIGINT' WHEN ColumnType = 'I' THEN 'INTEGER' WHEN ColumnType = 'MI' THEN 'INTERVAL MINUTE' WHEN ColumnType = 'MO' THEN 'INTERVAL MONTH' WHEN ColumnType = 'MS' THEN 'INTERVAL MINUTE(' || trim(DecimalTotalDigits ) || ') TO SECOND(' || trim(DecimalFractionalDigits ) || ')' WHEN ColumnType = 'PD' THEN 'PERIOD(DATE)' WHEN ColumnType = 'PM' THEN 'PERIOD(TIMESTAMP(n) WITH TIME ZONE)' WHEN ColumnType = 'PS' THEN 'PERIOD(TIMESTAMP(n))' WHEN ColumnType = 'PT' THEN 'PERIOD(TIME(n))' WHEN ColumnType = 'PZ' THEN 'PERIOD(TIME(n) WITH TIME ZONE)' WHEN ColumnType = 'SC' THEN 'INTERVAL SECOND' WHEN ColumnType = 'SZ' THEN 'TIMESTAMP(' || trim(DecimalFractionalDigits) || ') WITH TIME ZONE' WHEN ColumnType = 'TS' THEN 'TIMESTAMP' || '(' || trim(DecimalFractionalDigits) || ') FORMAT ''' || trim(ColumnFormat) || '''' WHEN ColumnType = 'TZ' THEN 'TIME WITH TIME ZONE' WHEN ColumnType = 'YM' THEN 'INTERVAL YEAR TO MONTH' WHEN ColumnType = 'YR' THEN 'INTERVAL YEAR' WHEN ColumnType = 'UT' THEN 'UDTType' END AS ColumnType ,CASE WHEN Nullable = 'N' THEN ' NOT NULL' ELSE '' END AS Nullable ,CASE WHEN CharType <> 0 THEN SUBSTR(ColumnFormat,2) ELSE '' END AS CharLength ,CASE WHEN CharType = 1 THEN ' CHARACTER SET LATIN' WHEN CharType = 2 THEN ' CHARACTER SET UNICODE' --WHEN CharType = 3 THEN 'CHARACTER SET KANJISJIS' --WHEN CharType = 4 THEN 'CHARACTER SET GRAPHIC' --WHEN CharType = 5 THEN 'CHARACTER SET KANJI1' --WHEN CharType = 0 THEN 'XYZ' -- others, not covered ELSE '' END AS CharacterSet ,CASE WHEN CharType = 1 AND UpperCaseFlag = 'U' THEN ' UPPERCASE NOT CASESPECIFIC' WHEN CharType = 1 AND UpperCaseFlag = 'C' THEN ' CASESPECIFIC' WHEN CharType = 1 AND UpperCaseFlag = 'N' THEN ' NOT CASESPECIFIC' WHEN CharType = 1 AND UpperCaseFlag = 'B' THEN ' UPPERCASE CASESPECIFIC' ELSE '' END AS UpperCaseFlag ,CASE WHEN DefaultValue IS NOT NULL THEN ' DEFAULT ' || UPPER(DefaultValue) ELSE '' END AS DefaultValue ,CASE WHEN Compressible = 'C' THEN ' COMPRESS ' || CASE WHEN trim(CompressValueList)='()' THEN '' ELSE trim(CompressValueList) END WHEN Compressible = 'N' THEN '' END AS CompressValueList FROM DBC.Columns C WHERE C.DATABASENAME ='YOURDATABASE' AND C.TABLENAME LIKE '%YOURTABLEGROUP%' ) T ) U INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON U.DATABASENAME = HB.DATABASENAME AND U.TABLENAME = HB.TVMNAME UNION -- * * * * THE PRIMARY INDEX PART * * * SELECT( HBHR + CAST(0.06 +CAST(PIPOS/10000.0 AS DECIMAL(38,18)) AS DECIMAL(38,18)) ) AS ORDERNR, CAST ( CASE WHEN PIPOS=1 THEN INDEXTYPE||'(' ELSE '' END || COLUMNNAME || CASE WHEN PIPOS=MXPIPOS THEN '' ELSE ',' END || CASE WHEN PIPOS=MXPIPOS THEN ')' ELSE '' END AS VARCHAR(3000) ) AS THESTRING FROM ( SELECT CAST( i.ColumnPosition AS DECIMAL(38,18) ) AS PIPOS , MAX(PIPOS) OVER (PARTITION BY i.DATABASENAME, i.TABLENAME ORDER BY 1 ) AS MXPIPOS , i.DATABASENAME , i.TABLENAME , CASE WHEN i.UNIQUEFLAG = 'Y' THEN 'UNIQUE PRIMARY INDEX' ELSE 'PRIMARY INDEX' END AS INDEXTYPE , i.COLUMNNAME (VARCHAR(2000)) AS COLUMNNAME , i.COLUMNPOSITION FROM DBC.INDICES i INNER JOIN DBC.TABLES t ON i.TABLENAME = t.TABLENAME AND i.DATABASENAME = t.DATABASENAME WHERE i.INDEXTYPE IN ('P', 'Q' ) AND i.DATABASENAME ='YOURDATABASE' AND i.TABLENAME LIKE '%YOURTABLEGROUP%' ) P INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON P.DATABASENAME = HB.DATABASENAME AND P.TABLENAME = HB.TVMNAME UNION -- * * * THE PARTITION PART * * * -- IF ONE-LEVEL, conventional RANGE or CASE SELECT ( HBHR + CAST(0.07 AS DECIMAL(38,18)) ) AS ORDERNR, COALESCE( 'PARTITION BY ( ' || CAST( SUBSTR(PART0,2 ,index(PART0,'N((') ) || SUBSTR(PART1, 2) || SUBSTR(PART2, 1, index(PART2,'UNKNOWN')+7 ) AS VARCHAR(3000) ) ||')' , '') AS THESTRING FROM ( SELECT DATABASENAME, TABLENAME, substr(ConstraintText, index(ConstraintText,'((')+1 ,index(ConstraintText,'((H')+1 ) AS PART0, ConstraintText, substr(ConstraintText, 17, index(ConstraintText,'BETWEEN') -17 ) AS PART1, substr(ConstraintText, index(ConstraintText,'BETWEEN') , index(ConstraintText,'UNKNOWN') ) AS PART2 FROM DBC.IndexConstraints t WHERE ConstraintType = 'Q' AND ( ConstraintText LIKE '%RANGE_N%' OR ConstraintText LIKE '%CASE_N%' ) AND DATABASENAME = 'YOURDATABASE' and TABLENAME LIKE '%YOURTABLEGROUP%' AND ( SUBSTRING(ConstraintText FROM 1 FOR 13) < 'CHECK (/*02*/' OR SUBSTRING(ConstraintText FROM 1 FOR 13) > 'CHECK (/*15*/' ) ) P INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON P.DATABASENAME = HB.DATABASENAME AND P.TABLENAME = HB.TVMNAME UNION -- IF TWO-LEVEL , conventional RANGE or CASE SELECT ( HBHR + CAST(0.07 AS DECIMAL(38,18)) ) AS ORDERNR, COALESCE( 'PARTITION BY ( '|| CAST( SUBSTR( ConstraintText, index(ConstraintText,'/*02*/')+7, index( SUBSTR( ConstraintText, index(ConstraintText,'/*02*/')+7 ) ,'IS NOT NULL AND ')-2 ) AS VARCHAR(3000) ) || ',' || CAST( SUBSTR( SUBSTR( ConstraintText, index(ConstraintText,'IS NOT NULL AND') +16) , 1, index( SUBSTR( ConstraintText, index(ConstraintText,'IS NOT NULL AND') +16) , ' IS NOT NULL )' ) ) AS VARCHAR(3000) ) || ' ) ' , '') AS THESTRING FROM DBC.IndexConstraints T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME WHERE ConstraintType = 'Q' and ConstraintText LIKE '%/*02*/%' UNION -- IF THREE-LEVEL , conventional RANGE or CASE SELECT ( HBHR + CAST(0.07 AS DECIMAL(38,18)) ) AS ORDERNR, COALESCE( 'PARTITION BY ( '|| LEVEL1F || ',' || LEVEL2F || ',' || LEVEL3F || ' ) ' , '') AS THESTRING FROM ( sel ConstraintText, t.DATABASENAME, t.TABLENAME, CAST( SUBSTR( ConstraintText, index(ConstraintText,'/*03*/')+7, index( SUBSTR( ConstraintText, index(ConstraintText,'/*03*/')+7 ) ,'IS NOT NULL AND ')-2 ) AS VARCHAR(3000) ) AS LEVEL1F, CAST( SUBSTR( SUBSTR( ConstraintText, index(ConstraintText,'IS NOT NULL AND') +16) , 1, index( SUBSTR( ConstraintText, index(ConstraintText,'IS NOT NULL AND') +16) , ' IS NOT NULL )' ) ) AS VARCHAR(3000) ) AS LEVEL2i , CAST( SUBSTR( LEVEL2i ,1, index(LEVEL2i,'IS NOT NULL AND') -1) AS VARCHAR(3000) ) AS LEVEL2F, CAST( SUBSTR( LEVEL2i, character_length(trim( LEVEL2F))+17) AS VARCHAR(3000) ) AS LEVEL3F FROM DBC.IndexConstraints t WHERE ConstraintType = 'Q' and ConstraintText LIKE '%/*03*/%' AND t.DATABASENAME = 'YOURDATABASE' and t.TABLENAME LIKE '%YOURTABLEGROUP%' ) P INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON P.DATABASENAME = HB.DATABASENAME AND P.TABLENAME = HB.TVMNAME UNION -- IF ODD "CAST" OR "DIRECT" TYPE SELECT ( HBHR + CAST(0.07 AS DECIMAL(38,18)) ) AS ORDERNR, COALESCE( 'PARTITION BY ( '|| LEVEL1F || ' ) ' , '') AS THESTRING FROM ( sel ConstraintText, DATABASENAME, TABLENAME, CAST ( SUBSTR( SUBSTR( ConstraintText, index(ConstraintText,'((')+1 ) , 1, index(SUBSTR( ConstraintText, index(ConstraintText,'((')+1 ) ,'BETWEEN')-1 ) AS VARCHAR(3000) ) AS LEVEL1F FROM DBC.IndexConstraints t WHERE ConstraintType = 'Q' and ConstraintText NOT LIKE '%/*%' and ConstraintText NOT LIKE '%RANGE_N%' and ConstraintText NOT LIKE '%CASE_N%' AND DATABASENAME = 'YOURDATABASE' and TABLENAME LIKE '%YOURTABLEGROUP%' ) P INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON P.DATABASENAME = HB.DATABASENAME AND P.TABLENAME = HB.TVMNAME UNION -- * * * THE FINAL SEMICOLON , conventional RANGE or CASE * * * SELECT ( HBHR + CAST(0.08 AS DECIMAL(38,18)) ) AS ORDERNR, CAST( ';' AS VARCHAR(3000) ) AS THESTRING FROM DBC.TABLES T INNER JOIN ( sel D.DATABASENAME, T.TVMNAME, CAST( HASHBUCKET(HASHROW(T.TVMId)) AS DECIMAL(38,18) ) AS HBHR from dbc.Databases2 D INNER JOIN dbc.tables2 T ON D.DataBaseID = T.DataBaseID where T.TVMNAME LIKE '%YOURTABLEGROUP%' AND D.DATABASENAME ='YOURDATABASE' ) HB ON T.DATABASENAME = HB.DATABASENAME AND T.TABLENAME = HB.TVMNAME ORDER BY 1 ASC ;