Tip - Selectively limit number of rows loaded in LOAD script

Tip - Selectively limit number of rows loaded

Although the EDIT SCRIPT\DEBUG\LIMITED LOAD feature can restrict the number of rows return from all LOAD statements in a script, it has its limitations:

·        It limits every LOAD statement in the script equally i.e. no selectivity

·        It does not remember the last setting e.g. it always defaults to 10

·        It cannot be turned on by default

·        It limits the rows returned from SQL SELECT after the database has done all the work to prepare a larger number of rows

·        The dialog box makes the LOG entry window very small and therefor of limited use

·        It cannot be parameterized to allow a complete run of QVW tasks to be executed using limited or empty LOAD statements to allow a syntax and semantic check across the whole application

·        It can substantially slow down script execution

 

The tips below allow more flexibility in limiting rows and resources used on a per LOAD basis.

 

Set up the following variables:

 

let v_max_rows =100;
//let v_max_rows =;  // use this to turn it off

let v_limit_number_of_rows_sql = if( IsNull (v_max_rows), NULL, 'FETCH FIRST ' & v_max_rows & ' ROWS ONLY' ) ;

let v_limit_number_of_rows_qvw = if( IsNull (v_max_rows), ' /* dont restrict row count */', ' FIRST ' & v_max_rows & ' ' ) ;
 

 

And use them as described in LOAD statement examples below. Now, when you use RELOAD, you all your LOAD statements will be limited to XXX rows.

 

To turn off this feature, simply set the ‘v_max_rows‘ variable to NULL (see examples above):

               let v_max_rows =; 

Notes:

1.      Use of QVW ‘FIRST’ syntax does not prevent optimized loads

2.      Although ‘FIRST’ could also be used in SQL LOADs, it would apply after DB2 has sent all the rows across so it’s best to use DB2 specific ‘FETCH FIRST xxx ROWS ONLY’ syntax embedded in the SELECT

3.      The QVD FIRST also works within a CONCATENATE LOAD etc.

 

For LOAD from QVD:

       $(v_limit_number_of_rows_qvw)

       load
             etc
       FROM
             [filename.QVD] (qvd)
       WHERE
             etc
       ;
For LOAD FROM SQL:

LOAD

etc

;

SQL

SELECT

etc

$(v_limit_number_of_rows_sql)
with UR;

 

 

If your script uses DEBUG MODE’s LIMIT LOAD feature and FIRST XXX commands, you may encounter errors if you set your LIMIT LOAD value lower than any hardcoded FIRSTXXX syntax

 

E.g.

FIRST 1000

LOAD *

<some table or another>

 

And DEBUG LIMIT LOAD=999

 

This combination will generate an error similar to below:

Script line error:

claim:

 FIRST 1000

LOAD

     *,

     CLM_ID as CLM_ID_MATCH

FROM

     [..\QVD\Claim_Weekly_Valid.QVD] (qvd)

 

 

QVW’s warns that it’s been asked for two contradictory things i.e. limited the load to 999 rows and limit to 1000 rows.

 

QVW has no issues with setting LIMIT LOAD to a higher value than any stated FIRST XXX syntax e.g. LOAD LIMIT=2000 and FIRST 1000 combination issues no error and the FIRST XXX statement will take precedent

 

Note:  Neither LIMITED LOAD or FIRST keywords can reduce the results returned from the BINARY keyword.