SHOW TABLE tablename WITH STATISTICS; ?

Database

SHOW TABLE tablename WITH STATISTICS; ?

I know the above does not work but I want to extract the DDL of an existing table and the statistics collected on it in a rerunnable form. i.e. returns a statement like

CREATE TABLE databasename.tablename ....;
COLLECT STATS databasename.tablename ON INDEX (columnname, ...);
COLLECT STATS databasename.tablename ON COLUMN(columnname, ...);
COLLECT STATS databasename.tablename ON COLUMN(columnname, ...);

HELP STATS does not return a rerunnable statment. There is no SHOW STATS.

Is the only option to build the COLLECT STATS statements from the DBC tables - IndexStats, ColumnStats & MultiColumnStats.

Does anyone have SQL to do this?

I am aware that I can use CREATE TABLE AS ... WITH STATISTICS but this is not what I am after.
1 REPLY

Re: SHOW TABLE tablename WITH STATISTICS; ?

getting the ddl for the table is easy just query the dbc.tables
SELECT * FROM DBC.TABLES WHERE TRIM(TABLENAME)='YOURTABLENAME' AND TRIM(DATABASENAME)='YOURDBNAME' AND TRIM(TABLEKIND)='T' ;

Another Approach:
Create a fastexport or bteq export with the following query:

SELECT
'SHOW SELECT * FROM '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
FROM DBC.TABLES WHERE TRIM(DATABASENAME)='NDWXTL_LD' AND TRIM(TABLEKIND)='T' ;

Then create a bteq on the fly with the body of the bteq as the output of the export file.

Then run the bteq and redirect the output to a log file.

The logfile will contain DDL of all the tables present in 'NDWXTL_LD' database.

To get the statistics information its possible but it is hard to get it by querying the dbc tables.
So i will suggest try to use the above approach once more

use HELP STATS IPFRDEV_T.IPFR003_CURR; in the fastexport .
Then just work a little bit with UNIX to read the last column ans just concat it with
'collect statistic . on '

Issue with this approach is u don't know if it was collected on INDEX/COLUMN.

Hope It helps!!
Jeet