Originally posted by: karthikpc

HI all,

I am using -UPDATE OFF cmd in RUN function to insert rows into database. I am able to insert data but it is taking lot of time(CPU time and processing time) and it is very expensive. I have been searching for some information on -UPDATE OFF/ONLY cmd and I found that, KEY has to be define in database interface designer while creating MDQ files. So i would like to know whether KEY is always mandatory to assign while creating MDQ+(because i am able to insert row data with out key specified in the MDQ)+ or is there any other reasons which are making my maps very expensive. Please let me know if anybody has this performance issues while accessing database from WTX and the solutions which helped you to to achive best results.

OS: Z/OS - Mainframe
WTX - Version - 8.1
database - DB2

Thanks&Regards,
Karthik
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: TarunB

Key columns only need to be set when using the -UPDATE or -UPDATE ONLY commands. -UPDATE OFF is default anyway if -UPDATE or -UPDATE ONLY are not specified. One thing to be aware of with -UPDATE OFF is unique key violation.

It's not clear why your maps are inefficient and without analysing your code or further detailed information we can only make generalised statements. You could try the following:

1) Try to work out where the inefficiency is in the flow. For example, output to a file instead of the DB : this will tell you if the delay is in the map or the DB connection.

2) Use burst mode if inserting large numbers of rows in each map execution. Or consider using DB2 bulk load utility.

3) Turn off tracing (map especially but also DBLs).

4) Have a DBA analyse the connections that the map is making during execution. There could be lock constraints causing inserts to queue up.

etc.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: karthikpc

Tarun,

We have tried with all the points which you specified below. (i.e , trace off, write into a file instead of DB , DBA confirmed that there is no issues with the SQL query etc etc) . Its been identified that map performance is good but the cost involved in inserting data into table is costing us a lot of money. This is because of number of I/Os etc. we want to reduce these i/os. can you tell us how to put burst mode on output card( as far as i know, output will be always on burst).

Thanks&Regards,
Karthik
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange