New system stored procedure to diagnose corrupted tables and columns.

Hi all,

Check db process is usually running long and affecting performance.

This procedure reads every database page from disk into memory and performs various consistency checks.

In check mode, sp_iqcheckdb performs an internal consistency check on all IQ indexes and checks that each database block has been allocated correctly.
All available database statistics are reported. This mode reads all data pages and can detect all types of allocation problems and most types of index inconsistencies.
Check mode should run considerably faster than verify mode for most databases.

In verify mode, sp_iqcheckdb performs an intra-index consistency check, in addition to internal index consistency and allocation checking.
All available database statistics are reported.
The contents of each non-FP index is verified against its corresponding FP index(es).
Verify mode reads all data pages and can detect all types of allocation problems and all types of index inconsistencies.

Here is a new lightweight procedure to diagnose corrupted FP index of tables and columns in IQ16.

** IQ Version

• SAP IQ 16 SP10.10 and above
• SAP IQ 16 SP11.01 and above

** Usage

• call sp_iqcheckfpconsistency();
• call sp_iqcheckfpconsistency(‘<table-name>’);
• call sp_iqcheckfpconsistency(‘<table-name>’, ‘<column-name>’);
• call sp_iqcheckfpconsistency(‘<table-name>’, ‘<column-name>’, ‘<table_owner>’);

All parameters are optional. If no parameter is provided, it will check all columns of all SAP IQ tables.
Please be advised it may take long time if there are lots of tables and columns. If table name is given, but column name is not given, it will check all columns of the given table.
If table and column names are given, but owner is not given, it will check matched columns for all owners.

** Result

A result table will be returned to provide a summary report.
If errors are detected, detail error messages will be available in the SAP IQ message file.

[Example]
…
t115 *** Index I_ASIQ.NICASD89_ERR.ASIQ_IDX_T4219_C9_FP (Column SD89PROD(char)): No Errors Detected
t115 *** Index I_ASIQ.NICASD89_ERR.ASIQ_IDX_T4219_C5_FP (Column SD89PSNO(char)): No Errors Detected
barray t115 SD89SRBR: CheckColumnEBMCoverage
barray t115*** duplicate barray page in ridmap: 35
t115 *** I_ASIQ.NICASD89_ERR.ASIQ_IDX_T4219_C46_FP(char): multiple ridmap regions link to the same page.
t115 *** I_ASIQ.NICASD89_ERR.ASIQ_IDX_T4219_C46_FP RidMap:
barray t115 C46: SD89SRBR
barray t115 C46 BEGIN dump RidMap:
barray t115 C46 startRecid=1 endRecid=698880 block=1 hasNBit=1

** Associated KBA.
2287035 – SAP IQ: New system stored procedure to diagnose corrupted tables and columns

– https://launchpad.support.sap.com/#/notes/2287035

Best Regards,
Gi-Sung JAng