FDM - Full table scan causes DB2 SQL Error: SQLCODE=-904, SQLSTATE=57011, SQLERRMC=00C90084;00000100;DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We have issues obfuscating Mainframe DB2 z/OS tables using FDM 4.11 docker masking engine. 

We have DB extend issue that we cannot modify when creating the temp file/data. Furthermore, we've noticed with PARALLEL, and LARGETABLE SPLIT options are used, each FDM thread runs a full table scan, and consumes all the temp space for the database, causing the reported error below.

DB2 SQL Error: SQLCODE=-904, SQLSTATE=57011, SQLERRMC=00C90084;00000100;DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION OR US, DRIVER=4.31.10

We would like to prevent the full table scans, or limit them. 

Environment

FDM 4.11

Cause

The issue is seen when running the following Select statement:

SELECT "PRI_TEL_NO","SCNDY_TEL_NO","IR_NO","CON_ID" FROM "<DATABASE>"."<TABLE>" order by "IR_NO","CON_ID"

The problem is related to the order by. When using order by, if there is no primary key, we will run a full table scan.

ORDERBY - Decides whether the selected data will be ordered by the primary key column or not. Default = Y. For more information see, TDM 4.11 Reference - Masking Options

Resolution

FDM has no control regarding full table scans. It's always up to the database engine if the full scan will be used or not.  FDM can only control if the ORDER BY clause will be used or not. However, it's more about indexes than primary key (which has inherently the index) - the problem could also happen when using composite primary key so it isn't only about the index existence but also about the order of the columns in the primary key (and hence the index).

For this example, it's possible that the primary key is actually CON_ID, IR_NO, but the masking configuration is using IR_NO, CON_ID in the order by clause, which would make the index incompatible and unusable for such a query.

Broadcom's suggestions:

• Try running a job with the ORDERBY option disabled
• Reverse the Order By clause from IR_NO, CON_ID to CON_ID, IR_NO
• Or simply dropping the clause altogether

Another possibility, according to the customer, isn't the primary index, but the clustering index. The database engine would go to a tablespace scan, if the clustering ratio is less than 70%.

This simply appears to be a configuration issue or an environmental issue.