InfoSphere Optim

Hi,

Wondering if anyone has any tips for improving speed/efficiency for a long-running convert request.

The current result I am getting:

• MSSQL table with 18K rows
• 20 of 25 columns being converted
• 8 of 25 using Hash Lookup (I believe these are the culprit)
• Total convert time:  ~30 minutes

The table is indexed.
4 of the 8 lookup fields are using a custom C++ exit.
The other 4 of 8 are using Optim Hash Lookup.

Optim seems to take about 1 sec per every 10 rows.

I appreciate any feedback!

Regards

------------------------------
Trong Bo
Senior Environment Administrator
HealthNow New York
------------------------------

#InfoSphereOptim
#Optim

From a pure Optim settings perspective I would say start with these attached docs. Use them as reference. Experiment on non-prod systems. For what it's worth, you can modify some of the following configuration parameters shown below. I've found that we can significantly change the performance of Optim data movement inbound and outbound when we tweak these settings for specific use cases. I have not mentioned anything about the source MSSQL database or your network parameters. Those should be factored in as well, but that's not an Optim config. 

• Optim Workstation Personal settings.
  • Database Parallel Processing. I use 4. 
• Optim Workstation Product settings. 
  • General Settings Tab. (Max Fetch rows) I use 100-2500 depending on what i'm doing. 
  • General Settings Tab. (Max Extract Rows) 999999999999*
  • Database Settings Tab. (Commit frequency) I use 100-5000 depending on what i'm doing. 
  • Database Setting Tab. (Parallel Processing) I use 4. 
  • Database Settings Tab. (DBMS buffers) I usually don't mess with these. I just let Optim auto change them based on the other inputs above. 
  • Etc.. 
• Optim Server Settings. 
  • If you're using the LOAD tools, there a tons of optimization features there with those tools. i.e. Oracle Loader, SQL loader, etc... 

------------------------------
Danny Lankford
3M - IT Manager
------------------------------

Original Message:
Sent: Fri March 20, 2020 11:37 AM
From: Trong Bo
Subject: Convert Request Optimization

Hi,

Wondering if anyone has any tips for improving speed/efficiency for a long-running convert request.

The current result I am getting:

• MSSQL table with 18K rows
• 20 of 25 columns being converted
• 8 of 25 using Hash Lookup (I believe these are the culprit)
• Total convert time:  ~30 minutes

The table is indexed.
4 of the 8 lookup fields are using a custom C++ exit.
The other 4 of 8 are using Optim Hash Lookup.

Optim seems to take about 1 sec per every 10 rows.

I appreciate any feedback!

Regards

------------------------------
Trong Bo
Senior Environment Administrator
HealthNow New York
------------------------------

#InfoSphereOptim
#Optim

Thanks, Danny!  I'll give these a read!

------------------------------
Trong Bo
Senior Environment Administrator
HealthNow New York
------------------------------

Original Message:
Sent: Fri March 20, 2020 02:53 PM
From: Danny Lankford
Subject: Convert Request Optimization

From a pure Optim settings perspective I would say start with these attached docs. Use them as reference. Experiment on non-prod systems. For what it's worth, you can modify some of the following configuration parameters shown below. I've found that we can significantly change the performance of Optim data movement inbound and outbound when we tweak these settings for specific use cases. I have not mentioned anything about the source MSSQL database or your network parameters. Those should be factored in as well, but that's not an Optim config. 

• Optim Workstation Personal settings.
  • Database Parallel Processing. I use 4. 
• Optim Workstation Product settings. 
  • General Settings Tab. (Max Fetch rows) I use 100-2500 depending on what i'm doing. 
  • General Settings Tab. (Max Extract Rows) 999999999999*
  • Database Settings Tab. (Commit frequency) I use 100-5000 depending on what i'm doing. 
  • Database Setting Tab. (Parallel Processing) I use 4. 
  • Database Settings Tab. (DBMS buffers) I usually don't mess with these. I just let Optim auto change them based on the other inputs above. 
  • Etc.. 
• Optim Server Settings. 
  • If you're using the LOAD tools, there a tons of optimization features there with those tools. i.e. Oracle Loader, SQL loader, etc... 

------------------------------
Danny Lankford
3M - IT Manager

Original Message:
Sent: Fri March 20, 2020 11:37 AM
From: Trong Bo
Subject: Convert Request Optimization

Hi,

Wondering if anyone has any tips for improving speed/efficiency for a long-running convert request.

The current result I am getting:

• MSSQL table with 18K rows
• 20 of 25 columns being converted
• 8 of 25 using Hash Lookup (I believe these are the culprit)
• Total convert time:  ~30 minutes

The table is indexed.
4 of the 8 lookup fields are using a custom C++ exit.
The other 4 of 8 are using Optim Hash Lookup.

Optim seems to take about 1 sec per every 10 rows.

I appreciate any feedback!

Regards

------------------------------
Trong Bo
Senior Environment Administrator
HealthNow New York
------------------------------

#InfoSphereOptim
#Optim

Thanks, Danny Lankford, its really helps me a lot.

------------------------------
Dennis Weiss
------------------------------

Original Message:
Sent: Fri March 20, 2020 02:53 PM
From: Danny Lankford
Subject: Convert Request Optimization

From a pure Optim settings perspective I would say start with these attached docs. Use them as reference. Experiment on non-prod systems. For what it's worth, you can modify some of the following configuration parameters shown below. I've found that we can significantly change the performance of Optim data movement inbound and outbound when we tweak these settings for specific use cases. I have not mentioned anything about the source MSSQL database or your network parameters. Those should be factored in as well, but that's not an Optim config. 

• Optim Workstation Personal settings.
  • Database Parallel Processing. I use 4. 
• Optim Workstation Product settings. 
  • General Settings Tab. (Max Fetch rows) I use 100-2500 depending on what i'm doing. 
  • General Settings Tab. (Max Extract Rows) 999999999999*
  • Database Settings Tab. (Commit frequency) I use 100-5000 depending on what i'm doing. 
  • Database Setting Tab. (Parallel Processing) I use 4. 
  • Database Settings Tab. (DBMS buffers) I usually don't mess with these. I just let Optim auto change them based on the other inputs above. 
  • Etc.. 
• Optim Server Settings. 
  • If you're using the LOAD tools, there a tons of optimization features there with those tools. i.e. Oracle Loader, SQL loader, etc... 

------------------------------
Danny Lankford
3M - IT Manager

Original Message:
Sent: Fri March 20, 2020 11:37 AM
From: Trong Bo
Subject: Convert Request Optimization

Hi,

Wondering if anyone has any tips for improving speed/efficiency for a long-running convert request.

The current result I am getting:

• MSSQL table with 18K rows
• 20 of 25 columns being converted
• 8 of 25 using Hash Lookup (I believe these are the culprit)
• Total convert time:  ~30 minutes

The table is indexed.
4 of the 8 lookup fields are using a custom C++ exit.
The other 4 of 8 are using Optim Hash Lookup.

Optim seems to take about 1 sec per every 10 rows.

I appreciate any feedback!

Regards

------------------------------
Trong Bo
Senior Environment Administrator
HealthNow New York
------------------------------

#InfoSphereOptim
#Optim