Tired of parsing the output from Ferret SHOWBLOCKS and SHOWWHERE commands? You don’t need to do that anymore with the release of the SQL SHOWBLOCKS and SQL SHOWWHERE features. The SQL SHOWBLOCKS feature is available in Teradata Database 15.0, and SQL SHOWWHERE is available in 15.10. These new features allow you to easily extract SHOWBLOCKS and SHOWWHERE data, which can subsequently be queried for analysis using SQL. Not only is this information simpler to view, but the table-based data can easily be exported to third party tools for better view/analysis of the system-level information.
Unlike the Ferret utility, the SQL SHOWBLOCKS and SQL SHOWWHERE macros do not require special console privileges (DBCCONS or CNS supervisor window) to run. These SQL macros can be run through many user sessions at the same time, whereas the number of sessions that one can initiate through Ferret is limited to the number of CNS Supervisor window sessions that can be started.
SQL SHOWBLOCKS and SQL SHOWWHERE both employ the same two new system macros. CreateFsysInfoTable creates a target table to hold the file system information and PopulateFsysInfoTable populates the target table with system-level information for SHOWBLOCKS or SHOWWHERE. Once the target tables are populated with SHOWBLOCKS or SHOWWHERE rows, normal SQL queries can be run on those target tables and several system level details, such as Data Block Size Statistics, Cylinder and Block Level Compression related stats, Temperature and grade of the storage, can be obtained.
The example below shows how to create a target table to hold SHOWBLOCKS information:
EXEC DBC.CreateFsysInfoTable (‘SYSTEMINFO’, ‘SHOWBLOCKS_M’, ‘PERM’, ‘Y’, ‘SHOWBLOCKS’, ‘M’ );
- Creates the permanent target table ‘SHOWBLOCKS_M’ with fallback in the target database ‘SYSTEMINFO’ for capturing the SHOWBLOCKS’s medium (‘M’) display rows.
The example below shows how to populate the target table created above:
EXEC DBC.PopulateFsysInfoTable (‘PRODUCTION’, ‘CALLLOG_2015, ‘SHOWBLOCKS’, ‘M’ , ‘SYSTEMINFO’, ‘SHOWBLOCKS_M’);
- Populates the target table ‘SYSTEMINFO.SHOWBLOCKS_M’ with SHOWBLOCKS medium (‘M’) display rows of the input table ‘PRODUCTION.CALLLOG_2015’.
For more details on syntax, invocation and other requirements refer to the SQL Functions, Operators, Expressions and Predicates manual and the SQL SHOWBLOCKS and SQL SHOWWHERE Orange Book listed in Appendix A.
Figure1 shows the sample rows from a target table “SYSTEMINFO.SHOWBLOCKS_M” which stored SHOWBLOCKS information for a source table “PRODUCTION.CALLOG_2015”.
Figure 1: Sample SQL SHOWBLOCKS rows from a target table
The corresponding SHOWBLOCKS output collected from Ferret is shown in Figure 2.
Figure 2: SHOWBLOCKS /M output from Ferret
The sample SQL used to extract data from the target table, which was then used to create the graphs shown in Figure3 and Figure4, is presented below:
Note that these graphs represent multiple invocations of the PopulateFsysInfoTable macro over time into the target table ‘SYSTEMINFO.SHOWBLOCKS_M’ before running the above SQL.
Figure3: Estimated Compression Ratio by Date Figure 4 : Min, Max and Average DB Size values (in units of sectors) by Date
Does this run some sort of utility behind the scene (Like Ferret)?
If yes, it could possibly be very disruptive to execute such SQL's.
Could you please provide some additional details?
It will be super helpful for everyone.
It doesn't run Ferret in the background, but essentially performs the same type of work. The performance is not very disruptive, we have some bench mark results documented in the Orange Book (#2 in the list of Reference Manuals, you can refer to Page # 21 in that book). Suggest you go through those details documented in the OB.