Showing posts with label CPU Optimization. Show all posts
Showing posts with label CPU Optimization. Show all posts

Wednesday, 7 March 2012

Dynamic Variable with Dynamic Array Occurrence

Most of has used dynamic variables in natural, with 4.1 we can abuse it further by utilizing it in dynamic array as well. Probably I should have explained what came along with 4.1 features first but one of the biggest advantages of 4.1 features is its advantage to hold more than 32K of data in work area. In fact we can have 1GB (but please check with your environment support or DBA’s before you decide to invoke these features, it may snap your system if not wisely coded). The major advantage of dynamic variable with dynamic array occurrence is the elimination of “Index not within array structure” errors since we code it in such a way as to have a upper limit based on forecasted growth. And if used wisely, we can eliminate this error completely (but please make sure someone does a good code reviewJ). The option is a double edged sword: it not only eliminates “Index not within array structure” and loads or dynamically expands array data based on growth but with the negative fallout that if it exceeds 1GB your process falls over. So be very cautious on its usage.

The simple trick in a dynamic variable with dynamic array occurrence is to capture the current occurrence by just moving the *COUNTER variable to the counter variable to expand the dynamic variable & array. The result is – a characteristic of passed field is taken as characteristics of the result field for dynamic field. And it’s most efficient usage would be to use in loading reference-data.



A sample piece of code will look like this:



DEFINE DATA LOCAL
01 REF VIEW OF REFERENCE-DATA
  02 RD-KEY             /* 15 BYTES OF SUPER-DESCRIPTOR
  02 REDEFINE RD-KEY
     03 RD-KEY1 (A5)          /* FIRST HALF OF SUPER
     03 RD-DATA (A10)         /* SECOND HALF OF SUPER

01 #START-RD-KEY (A15)
01 #END-RD-KEY   (A15)
01 #CITY-ARRAY   (A/*) DYNAMIC
01 #CITY-COUNT   (I4)
01 #CITY-INDEX   (I4)
END-DEFINE
*
RESET #START-RD-KEY #END-RD-KEY #CITY-COUNT
COMPRESS 'MIAMI' INTO #START-RD-KEY LEAVING NO SPACE
COMPRESS 'MIAMI' H'FF' INTO #END-RD-KEY LEAVING NO SPACE
*
HISTOGRAM MULTI-FETCH OF 1000 REF FROM #START-RD-KEY
 TO #END-RD-KEY
  ADD 1 TO #CITY-COUNT /* Or Move *COUNTER
  EXPAND ARRAY #CITY-ARRAY TO (1:#CITY-COUNT) /* expands the array to counter
  MOVE RD-DATA TO #CITY-ARRAY (#CITY-COUNT) /* resultant variable takes characteristics of moving variable
END-HISTOGRAM
*
* Sample code to check if data is loaded
IF #CITY-COUNT > 0
  IF #CITY-ARRAY (*) EQ SCAN 'MIAMI'
/* SCAN is a new option in 4.1 and similar to examine but just checks for existence of a value
      WRITE 'MATCH FOUND FOR MIAMI'
  ELSE
      WRITE 'MATCH NOT FOUDN FOR MIAMI'
  END-IF
END-IF
END



If used wisely the advantages are limitless providing a good optimization in many fronts wherever applicable, probably opening the Pandora’s Box for another discussion (loading most frequent data to array for optimized performance :-)).




Wednesday, 25 January 2012

Natural – DB2 ROWSET POSITIONING

Its multiple Row processing in Natural-Db2 process which helps reduce cpu, increase throughput and save MIPS. This is in some way similar to Natural-Adabas MULTI-FETCH  parameter option where in we specify the multiple row selection when Natural issues the call to Adabas and the limitation of using 32K in view size or fetched record size.

Advantages of using the option
  • Improved throughput through fewer database access calls and lower network operations
  • Lower CPU consumption
So the Purpose of Rowset positioning is similar to MULTI-FETCH option in Natural-Adabas, a ROWSET POSITIONING offers to read multiple records with a single database call. At statement execution time, the runtime checks if a multi-fetch-factor greater than 1 is supplied for the database statement and the database call is prepared dynamically to read multiple records with a single database access into the buffer.

Prerequisite
  • The option will work only for DB2 version 8 and above only.
  • Set the compiler option DB2ARRY=ON (through inline program statement coded after END-DEFINE statement or through NOCOPT command in the Natural editor
  • Specify a list of receiving array fields in the INTO clause of the SELECT query. Please note for ROWSET option to work, the length of receiving fields should be same as length of the fields in DB2 table otherwise the process will fall over at the time of execution.
  • Specify a variable receiving the number of rows retrieved from the database via the ROWS_RETURNED Clause. Variable has to be defined as I4.
  • Specify the number of rows to be retrieved from the database by a single FETCH operation via the WITH ROWSET POSITIONING Clause with a value between 0 & 32767. Variable has to be defined as I4.





Looking at a sample program below:


DEFINE DATA LOCAL                                                 
01 NAME            (A20/1:10)                                     
01 ADDRESS         (A100/1:10)                                    
01 DATEOFBIRTH     (A10/1:10)                                      
01 SALARY          (P4.2/1:10)                                    
01 L§ADDRESS       (I2/1:10)                                      
01 ROWS            (I4)                                           
01 NUMBER          (I4)                                           
01 INDEX           (I4)                                           
END-DEFINE                                                        
OPTIONS DB2ARRY=ON                                                
ASSIGN NUMBER := 10                                               
SEL.                                                              
SELECT NAME, ADDRESS , DATEOFBIRTH, SALARY                        
       INTO  :NAME(*),                             /* <-- ARRAY   
             :ADDRESS(*) LINDICATOR :L§ADDRESS(*), /* <-- ARRAY   
             :DATEOFBIRTH(1:10),                   /* <-- ARRAY   
             :SALARY(01:10)                        /* <-- ARRAY   
      FROM EMPLOYEE
      WHERE NAME > ' '                                            
      WITH ROWSET POSITIONING FOR :NUMBER ROWS     /* <-- ROWS REQ
      ROWS_RETURNED :ROWS                          /* <-- ROWS RET
  IF ROWS > 0                                                      
    FOR INDEX = 1 TO ROWS                                 
      DISPLAY                                                     
              INDEX (EM=99) *COUNTER (SEL.) (EM=99) ROWS (EM=99)  
              NAME(INDEX)                                         
              ADDRESS(INDEX) (AL=20)                              
              DATEOFBIRTH(INDEX)                                  
              SALARY(INDEX)                                        
    END-FOR                                                       
  END-IF                                                          
END-SELECT                                                        
END




What the above program accomplished is reading 10 rows of data by a single call to EMPLOYEE table. The local field NUMBER helped in telling DB2 to fetch 10 rows satisfying the criteria in the SQL statement. The number of records returned by DB2 is saved in ROWS field.