This example uses the PopulateFSYSInfoTable macro when no target database name and table name is specified. Help text for Populatefsysinfotable macro is also updated.
exec populatefsysinfotable('dbc','classofsspool','showofsspace','s','','');
Output:
Date Time UserName TableID ParentQueryID ParentUserID HostID SessionNumber RequestNumber Total Space MB -------- --------------- ------------------------- ------------ -------------------- ------------ ----------- ------------- ------------- -------------------- 24/03/09 10:03:09.000000 LARGEOFSUSER C01010E80000 307191040986785124 1110 1025 1019 123 8200 24/03/09 10:03:09.000000 OFSSPOOL C01010E90000 307191040986785125 1111 1025 1021 123 8200 24/03/09 10:03:09.000000 LARGEOFSUSER C01010EA0000 307191040986785126 1110 1025 1019 124 16392 24/03/09 10:03:09.000000 OFSSPOOL C01010EB0000 307191040986785127 1111 1025 1021 124 16392 24/03/09 10:03:09.000000 LARGEOFSUSER C01012080000 307191040986785244 1110 1025 1019 125 40968 24/03/09 10:03:09.000000 OFSSPOOL C01012090000 307191040986785245 1111 1025 1021 125 39944 24/03/09 10:03:09.000000 LARGEOFSUSER C010120A0000 307191040986785246 1110 1025 1019 126 8200 24/03/09 10:03:09.000000 OFSSPLTABLE C020040B0000 307181040986784101 1112 1025 1023 123 8200 24/03/09 10:03:09.000000 OFSSPLTABLE C020040C0000 307181040986784102 1112 1025 1023 124 16392 24/03/09 10:03:09.000000 OFSSPLTABLE C020040D0000 307181040986784103 1112 1025 1023 125 40968 24/03/09 10:03:09.000000 DB C010040C0000 307191440848349038 1102 1025 1003 433 944415 24/03/09 21:52:23.000000 TEST E01000BF0000 307192541409624149 1108 1025 1000 3 4227312
The following command generates help text for the Populatefsysinfotable macro output.
help macro populatefsysinfotable;
Output:
Parameter Name Type Comment Nullable Format Title Max Length Decimal Total Digits Decimal Fractional Digits Range Low Range High UpperCase Default value Char Type IdCol Type UDT Name Parameter Dictionary Name Parameter SQL Name ------------------------------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ ------------------------------------------------------------ ------------- -------------------- ------------------------- ---------------------- ---------------------- --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ---------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- InputDataBaseName CV Name of the database from which show[blocks|where|compress|ofsspace] information is required for a table N X(4096) ? 8,192 ? ? ? ? N ? 2 ? ? InputDataBaseName InputDataBaseName InputTableName CV Table in InputDataBaseName for which show[blocks|where|compress|ofsspace] information is required for a table N X(4096) ? 8,192 ? ? ? ? N ? 2 ? ? InputTableName InputTableName Command CF This can be SHOWBLOCKS/SHOWWHERE/SHOWCOMPRESS/SHOWOFSSPACE N X(32) ? 32 ? ? ? ? N ? 1 ? ? Command Command DISPLAYOPTION CV This can be S/M/L depending on the type of display needed N X(1) ? 1 ? ? ? ? N ? 1 ? ? DISPLAYOPTION DISPLAYOPTION TargetDatabaseName CV Data base name containing TargetTableName table in which show[blocks|where|compress|ofsspace] information is stored N X(4096) ? 8,192 ? ? ? ? N ? 2 ? ? TargetDatabaseName TargetDatabaseName TargetTableName CV Table in TargetDatabaseName data base in which show[blocks|where|compress|ofsspace] information is stored N X(4096) ? 8,192 ? ? ? ? N ? 2 ? ? TargetTableName TargetTableName
This example shows use of the PopulateFSYSInfoTable macro when one specifies a target database name and table name. The output is redirected to the target table.
Create the target table manually or by using the CreateFsysInfoTable macro:
exec populatefsysinfotable('dbc','classofsspool','showofsspace','s','db','t1s');
The following command returns the row count.
sel count(*) from db.t1s;
Output:
Count(*)
-----------
12
Query the table.
sel * from db.t1s;
Output:
TheDate TheTime UserName TableID ParentQueryID ParentUserID HostID SessionNumber RequestNumber TotalSpaceMB -------- --------------- --------------------------- ------------ -------------------- ------------ ------ -------------------- -------------------- -------------------- 24/03/09 10:08:43.000000 LARGEOFSUSER C01010EA0000 307191040986785126 1110 1025 1019 124 16392 24/03/09 10:08:43.000000 LARGEOFSUSER C01012080000 307191040986785244 1110 1025 1019 125 40968 24/03/09 10:08:43.000000 OFSSPOOL C01012090000 307191040986785245 1111 1025 1021 125 40968 24/03/09 10:08:43.000000 LARGEOFSUSER C010120A0000 307191040986785246 1110 1025 1019 126 16400 24/03/09 10:08:43.000000 OFSSPLTABL C020040C0000 307181040986784102 1112 1025 1023 124 16392 24/03/09 10:08:43.000000 OFSSPLTABL C020040D0000 307181040986784103 1112 1025 1023 125 40968 24/03/09 10:08:43.000000 OFSSPLTABLE C020040B0000 307181040986784101 1112 1025 1023 123 8200 24/03/09 10:08:43.000000 OFSSPOOL C01010EB0000 307191040986785127 1111 1025 1021 124 16392 24/03/09 10:08:43.000000 OFSSPOOL C01010E90000 307191040986785125 1111 1025 1021 123 8200 24/03/09 10:08:43.000000 LARGEOFSUSER C01010E80000 307191040986785124 1110 1025 1019 123 8200 24/03/09 10:03:09.000000 DB C010040C0000 307191440848349038 1102 1025 1003 433 944415 24/03/09 21:52:23.000000 TEST E01000BF0000 307192541409624149 1108 1025 1000 3 4227312