DB2 Statistics and Optimization

The number one and two could be combined. Although, the statistics issue is the priority! We have a vendor program written in UNIBASIC to push and convert the UNIDATA files from one AIX server (where the UNIDATA files are) to the DB2 tables (another AIX server). The DATA DIRECT ODBC Bridge connects the two servers. Whenever the user or process updates anything on the UNIDATA file system the vendor's program captures the record(s) in the queue file, converts it and exports to the DB2 table(s). We don't have an ability to change the table DDL or anything else on DB2. The utility pushes the data extremely slow. The utility does not capture the time stamps or any update info. There is no statistics of how many records were inserted or deleted per minute / hour and which records were inserted / deleted / modified. We only had about 9,000 records moved over night. There is a STC_EXP_REC_EXPORT table in DB2 which represents the queue file and contains the file names and records keys, the number of records to be pushed. (1) I need to create the statistic of how many records and from which tables are being updated / deleted /inserted in DB2 and how long it takes to insert & update the record. (2) I need to create an optimized version of the following tables: The DDL and data samples are attached. MEMBER MEMBER_WORK_PERIOD MEMBER_WORK_PERIOD_FUND Also, all of the stored procedures and reports are pointing to these tables. This will be changed to point the new optimized tables. The above tables are generated by the vendor & can't be modified. The process should populate the data in the new structure once & update with the new records nightly. The new table structure should have a time stamps and an indicator with updates / deletes / inserts. ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform DB2 9, aix