13) Gen, CA-GEN, COOLGEN and DB2 related issues

GEN and DB2 Related issues The SQL generated by CA GEN, COOL:Gen is not always the best and in most cases there is an need for the following: • Creation of new index • Rewrite the READ action block to access existing indexes • Change table/index parameters • Need for partitioned tables Best Practice: Once the servers are ready for execution, the bind for the plans are done with EXPLAIN option. Each and every SQL is scanned for the following: • Does it result in a join, if so can it be avoided • Does it use an existing index, if not how to influence the SQL to make use of it • Would creation of an index improve the performance • Change of parameters (for eg: for batch jobs executing without any overlap, the lock can be escalated to table level and combined with appropriate acquire and release parameters in the bind step the performance can be improved).