Like the title says, we've just installed Data Migrator and I'm writing a load job in DM that takes 18 hours to run in WebFOCUS today.

I took the DM class, and worked through the job flow. The extract piece runs in less than 1/3 the time through WebFOCUS. The problem is the extract runs, the table is created in SQL, but nothing is loaded. I'm thinking it might be a permissions thing, but I'm not sure. Does anyone have any thoughts?

The flow is using "insert records from memory" with a commit=2000 and blocksize=2000. (Both values just picked out of the ether). We've run another load to the same SQL box, same settings (different input file) and it worked fine. The only difference was number of records.

05/15/2011 15:23:17 UATDM (ICM18122) Request - apollo/dda_mth (Owner: xxxxx) submitted.
05/15/2011 15:23:17 UATDM (ICM18741) apollo/dda_mth_stage type MS SQL Server New target
05/15/2011 15:23:17 UATDM (ICM18429) Issuing PREPARE
05/15/2011 15:23:17 UATDM (ICM18440) Request will process data via NON-Pass Through (NON-APT)
05/15/2011 15:23:17 UATDM (ICM18451) HOLD file will be created for output file named: SQLIN.
05/15/2011 15:23:17 UATDM 1
05/15/2011 15:23:17 UATDM 0 NUMBER OF RECORDS IN TABLE= 14294673 LINES=*******
05/15/2011 15:23:17 UATDM 0
05/15/2011 15:23:17 UATDM (ICM18054) Issuing CREATE and DROP TABLE for dda_mth_stage
05/15/2011 15:23:17 UATDM (ICM18701) Bulk Insert active. INSERTSIZE = 20000
05/15/2011 15:23:17 UATDM (ICM18743) Starting Load
05/15/2011 15:23:17 UATDM 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
05/15/2011 15:23:17 UATDM REFERENCE...AT TRANS 20000
05/15/2011 15:23:17 UATDM (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 08S01
05/15/2011 15:23:17 UATDM : Microsoft OLE DB Provider for SQL Server: [08S01] Connection failure
05/15/2011 15:23:17 UATDM (FOC1415) COMMIT WORK ERROR. : DDA_MTH_STAGE
05/15/2011 15:23:17 UATDM (ICM18745) Commit forced at: 20000 for 20000 row(s)
05/15/2011 15:23:17 UATDM (ICM18744) Ending Load
05/15/2011 15:23:17 UATDM (ICM18040) Return Code = 1415
05/15/2011 15:23:17 UATDM (ICM18076) Request: apollo/dda_mth - finished processing
05/15/2011 15:23:17 UATDM (ICM18007) CPU Time : 2023109
05/15/2011 15:24:21 UATDM UATDM: Status
05/15/2011 15:24:21 UATDM Command executed successfully
05/15/2011 15:27:11 UATDM Action not available for your admin level
05/15/2011 15:27:35 UATDM Action not available for your admin level
05/15/2011 15:28:06 UATDM Action not available for your admin level
05/15/2011 15:29:42 UATDM Request "apollo/dda_mth" was created/updated successfully for User:"dmkempi"
05/15/2011 16:18:15 UATDM (ICM18016) Request apollo/dda_mth submitted. Please, wait for request to
05/15/2011 16:18:15 UATDM complete.
05/15/2011 16:18:15 UATDM (ICM18762) Job ID: 20110515153043_2ada2cc6
05/15/2011 16:18:15 UATDM (ICM18764) Request apollo/dda_mth failed; RC = 1415

I think you are on the right track that it is a permissions thing.

The log shows your request is using NON-APT which means that all the data gets written to a temp disk file before the load.

If the source is one or more relational tables in the same database, you can get better throughput if APT (Automatic Pass Through) can be used by avoiding source transformations. BTW, it looks like you have a block size of not 2,000 but 20,000. The job is failing when it tries to commit the first block to the table, so check that the user id has access to the database and to write to the target table.

OTOH if the source is flat files, or tables in different databases, or for some other reason the query must use non APT, since that creates a file anyway, you may be better of using Bulk Load via Disk File.

Also the "Action not available for your admin level" indicates that the user id doesn't have permission for some server action. Are you running a stored procedure? You may need to be an APP or SERVER Admin to perform the actions.

Thanks Clif. I also tried backing off the COMMIT and BLOCKSIZE, and then it ran, so maybe it was memory issue. Strange how its throwing the admin level warnings though.....