DataStage Job fails with Error message UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Problem:

A DataStage job that accesses SQL Server using ODBC receives the following error when try to UPDATE or DELETE records:

ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 1,934: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 8,180: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (CC_OdbcDBStatement::executeDelete, file CC_OdbcDBStatement.cpp, line 1,278)

Cause

When the configuration was made for the ODBC database connection, one of the following options was not set:

Windows: Enabled Quoted Identifiers

Unix: QuotedId

Resolving the problem

Windows:

Edit the ODBC database configuration through the Administrative Tools, ODBC Data Sources, and check Enabled Quoted Identifiers

Here is how to enable for Wie Protocol and Native Wire Protocol drive:

For SQL Wire Protocol drive, Click Advance tab and check Enabled Quoted Identifiers

For SQL Server Native Wire Protocol drive,  click on Advance Tab and check  Enabled Quoted Identifiers.

Linux:

Determine the location of the .odbc.ini by examining the Environment Variable Settings Log entry in the DataStage job log. Look for System Variable ODBCINI

Edit the .odbc.ini and set QuotedId=Yes

Here is an example:

[DsodbcM]
Driver=#BRANDED_ODBC_DIR#/lib/VMmsss24.so
Description=DataDirect 6.0 SQL Server Wire Protocol
Address=193.128.90.32,1433
AnsiNPW=Yes
Database=Dsodbc
LogonID=dsqa
Password=dsqa
QuotedId=Yes

Reference:

IBM Online Document: http://www-01.ibm.com/support/docview.wss?uid=swg21587480

Set up ODBC Connection For IIS DataStage (v.8.5)

Configure Access to ODBC Data Source For IIS DataStage (v8.5, Window Server)

Thie following information can also be found here.

From the Engine Tier;
· Launch the dos Prompt as Adminstratior
· Change to cd c:\windows\syswow64
· Lounch odbcad32.exe

 

     C:\Windows\SysWOW64>odbcad32.exe

 

 



· Select a ODBC driver that you want to use, for example, SQL server. Make sure to only use drivers that are provided by IBM

 

 

· Define the DSN Connection by select the System DSN tab, then select ADD. Give the DSN a name, and provide the server, user id and password. You can also specify a default database.

 

When you are done, make sure to test the connection. It all is fine, you should be able to see the DSN from the IIS components such as DataStage or Information Analyzer.

 

Related Note:

• Configuring the DataStage v8.5 Parallel Engine On Windows
• Start WebSphere InfoSphere Server Using Administrator User in Window 2008 Server

 

Find Current Running SQL Statements In Oracle

List Curent Running Queries From Oracle Database Serv er

 

Here was a query I used to check the queries which were submitted by the module “ASBAgent.exe” (the datastage ASB Agent, if you use DataStage). You can remove the module clause and the username clause to see all running queries .

 

 

SELECT first_load_time,
  module,
  sql_text,
  username,
  disk_reads_per_exec,
  buffer_gets,
  rows_processed,
  hit_ratio
  FROM
(SELECT module,
  sql_text ,
  u.username ,
  round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec,
  s.buffer_gets ,
   100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio,
  s.first_load_time
FROM sys.v_$sql s, sys.all_users u
WHERE s.parsing_user_id=u.user_id
  and UPPER(u.username) ='SCOTT'
  and module = 'ASBAgent.exe'
) s
order by first_load_time desc;

 

Related Notes:

• Find Oracle Table Size, Number Of Columns and Rows
• Retrieve Oracle Table Column Name And Data Type

•   

Configuring DataStage v8.5 Parallel Engine (Windows)

Configuring the DataStage v8.5 Parallel Engine On Windows

The information can be found in this document. I added a few points for Windows Server related steps since I am not familiar with Windows Servers.

If the parallel engine is installed on a computer that runs Microsoft Windows Server, Standard or Enterprise edition, you can configure the parallel engine for Microsoft Windows Server, 32-bit or 64 bit editions, by editing the Windows Registry and by changing system settings that control available memory. The Windows Registry includes keys that control desktop heap size, swap area size, the number of available TCP connections, and memory management.

1. Before Starting Configuration

• Log in to Windows Server as the Administrator.
• Start the Registry Editor, In Window Server 2008, open the DOS prompt as Administrator (right click on the dos prompt and select “Run as administrator”. From the DOS prompt, enter regedit
                
    C:\Windows\system32>regedit


• Back up the Windows Registry. If you plan to make several changes (for example, to configure the parallel engine), create a single backup before you make all the changes.  To backup the registry, From the Registry Editor, select File -> Export and save the registry to a file.

            

2. Configuring the Windows Registry: Enabling auto-tuning for paged pool size

Enable auto-tuning for the paged pool size. Enabling auto-tuning ensures that sufficient memory is available for paged pools.

The Windows Server kernel allocates memory in pools. These pools are known as the paged pool and the non-paged pool. Performance degradation and server instability might result if the memory for these pools is exhausted. To avoid this situation, you can enable auto-tuning at server startup by editing the PagedPoolSize registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management registry subkey.

2.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

(2.) in the right pane of the Registry Editor, right-click PagedPoolSize, and click Modify.
(3.) in the Base area, select Hexadecimal.
(4.) In the Value data field, specify 0 (zero). If 0 is already specified, auto-tuning is enabled, and you do not need to change this value. If you must specify a value other than 0, the value data for SystemPages must be 0.
(5.) Click OK.

3. Configuring the Windows Registry: Enabling auto-tuning for system page size

Enable auto-tuning for the system page size. Enabling auto-tuning ensures that sufficient memory is available for page tables and page table entries.

To support virtual to physical address translation, the Windows operating system uses page tables and page table entries (PTEs). These tables reside in kernel memory. If not enough memory is allocated for the page tables, Windows might fail to create processes, threads, and I/O buffers. Because the parallel engine creates many processes to run a job, jobs will fail at startup or during run time if Windows does not have enough resources to create processes.

You can enable auto-tuning at server startup by editing the SystemPages registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management registry subkey.

3.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

(2.) In the right pane of the Registry Editor, right-click SystemPages, and click Modify.
(3.) In the Base area, select Hexadecimal.
(4.) In the Value data field, specify 0 (zero). Typically, specifying 0 for this value provides sufficient memory. If the available PTEs are exhausted, however, you can specify the maximum value, 0XFFFFFFFF. If you must specify a value other than 0, the value data for PagedPoolSize must be 0.
 (5.) Click OK.

4 Configuring the Windows registry: Setting the threshold for de-committing memory

Set the threshold for de-committing memory. On computers that have 1 GB or more of RAM, setting the threshold to the suggested value improves memory management.

When Windows frees memory at a specific address, the memory can remain committed, or it can be de-committed and marked as reserved. You can improve memory management on computers that have 1 GB or more of RAM by editing the HeapDeCommitFreeBlockThreshold registry value in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager registry subkey. This registry value specifies the number of contiguous bytes of memory (around the address of the freed memory) above which the block of memory is de-committed.

4.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

(2.) Specify the value data for HeapDeCommitFreeBlockThreshold registry value:
(3.) If the registry value does not exist, click Edit > New > DWORD Value, and type the name of the registry value.
(4.) In the right pane of the Registry Editor, right-click the registry value, and click Modify.
(5.) In the Base area, select Hexadecimal.
(6.) In the Value data field, specify 0x000400.
(7.) Click OK.

5 Configuring the Windows registry: Increasing the noninteractive desktop heap size

Increase the noninteractive desktop heap size to ensure that a sufficient number of processes can be created and run concurrently.

DataStage® processes are created in a specific desktop heap. Each process consumes a small amount of memory from the desktop heap. If you expect to run many InfoSphere DataStage jobs concurrently, increase the size of the noninteractive desktop heap to allow more processes to be created and run concurrently.

You increase the size of the noninteractive desktop heap by editing the SharedSection parameter string in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems registry subkey. The SharedSection parameter string is located in the Windows registry value and uses the following format to specify heap size:

SharedSection=xxxx,yyyy,zzzz

Where:

• · xxxx specifies the maximum size of the system-wide heap (in kilobytes)
• · yyyy specifies the size of each desktop heap
• · zzzz specifies the size of the desktop heap that is associated with a noninteractive Windows instance

5.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems

(2.) In the right pane of the Registry Editor, right-click the Windows registry value, and click Modify.

(3.) In the Value data field, locate the SharedSection parameter string, and change the last SharedSection parameter from 512 to 2048. For example, specify the following values for this parameter:

   SharedSection=1024,3072,2048

 If you cannot change the size of the desktop heap to 2048, try setting the value to 1024. For example, specify the following values for this parameter:

SharedSection=1024,3072,1024
 (4.) Click OK.

6. Configuring the Windows registry: Specifying TCP/IP settings

Specify settings for TCP/IP in the Windows Registry to ensure that the required number of parallel processes can run concurrently.

You specify TCP/IP settings by editing the following registry values in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters registry subkey:

MaxUserPort

Specifies the maximum port number for ephemeral TCP ports.

TcpNumConnections

Specifies the maximum number of concurrent connections that TCP can open. This value significantly affects the number of concurrent osh.exe processes that are allowed. If the value for TcpNumConnections is too low, Windows cannot assign TCP ports to stages in parallel jobs, and the parallel jobs cannot run.

These keys are not added to the registry by default.

6.1 Procedure

(1.) In the Registry Editor, select the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

(2.) Specify the following registry values: MaxUserPort and TcpNumConnections.

a. If the registry value does not exist, click Edit > New > DWORD Value, and type the name of the registry value.
b. In the right pane of the Registry Editor, right-click the registry value, and click Modify.
c. In the Base area, select Hexadecimal.
d. In the Value data field, specify the following values:
    Registry value name                         Value data                          Valid range
     MaxUserPort                                      65534                                 5000 - 65534 (decimal)
    TcpNumConnections                        65534                                 0 - 0xfffffe (65534 decimal)
e. Click OK.

7 Configuring Windows: Changing swap area size

Change the size of the swap area to improve performance.

7.1 Procedure

(1.) Click Start > Control Panel > System.
(2.) In the System Properties window, click the Advanced tab.
(3.) In the Performance area, click Settings.
(4.) In the Performance Options window, select the Advanced tab.
(5.) In the Virtual memory area, click Change.
(6.) In the Virtual Memory window, specify a value for Maximum size. Set this value to be one to one- and-a-half times larger than the value for physical memory.
(7.) Click Set.
(8.) Click OK three times, and then close the Control Panel window.

 8. Configuring Windows: Enable Physical Address Extension (PAE)

Enable /PAE switch. Edit the boot.ini file to remove the /3GB switch and, if necessary, add the /PAE switch.

By default, Windows reserves 2 GB of memory for the kernel and 2 GB of memory for user mode programs. When the /3GB switch is specified in the boot.ini file, 3 GB of memory is reserved for user mode programs, and the amount of kernel memory is reduced by 1 GB. This setting results in an insufficient amount of memory for the kernel. To resolve this problem, remove the /3GB switch, if present from boot.ini file(for Windows Server 2000 or 2003).

In addition, on servers that have more than 4 GB of physical memory installed, specify the /PAE switch in the boot.ini file. For Windows 2008 server, use the BCDEDIT.exe tool (see section 11.8.1 below). The /PAE switch specifies physical address extensions that allow a 32-bit processor to access memory that is above the 4 GB limit.

8.1 Procedure

(1.) Click Start > Control Panel > System.
(2.) In the System Properties window, click the Advanced tab.
(3.) In the Startup and Recovery area, click Settings.
(4.) Click Edit to edit the boot.ini file.
(5.) Remove the /3GB switch, if present.
(6.) On servers that have more than 4 GB of physical memory installed, add the /PAE switch to the boot.ini file.
(7.) Save and close the boot.ini file.
(8.) Click OK twice, and then close the Control Panel window.

Note: There is no boot.ini in Windows Server 2008. To enable PAE in Windows 2008 server, use the Boot Configuration Data Editor, BCDEDIT.EXE, which is located in c:\windows\system32\ and follow the following steps:

(1.) Open Command Prompt with elevated privileges (Run as Administrator)
(2.) Execute the following command

      BCDEDIT /SET PAE ForceEnabl

(3.) To verify, run BCDEDIT without any switch:

        BCDEDIT

You should see “PAE ForceEnabl” at the end of the screen.

9 Restart Windows Server

After you make changes to the Windows Registry, restart Windows to apply your changes.

Reference: 

• Configuring the parallel engine (Windows Server)
• Boot Configuration Data Editor Frequently Asked Questions
• Other references on the web

 

Start WebSphere InfoSphere Server Using Administrator User in Window 2008 Server

Start WebSphere InfoSphere Server Using Administrator User in Window 2008 Server

To start the WebSphere InfoSphere server, you must start it with administrator privileges (as an administrator). Otherwise, the services will not start.
To start the services using administrator, from the start menu, click on All Program and then click on IBM WebSphere ->Profile ->InfoSphere

Highlight Start the server and right mouse click. Select “Run as Administrator”. In Window server 2008, even though the user is belong to the local administrator group, if you do not select Run as Administrator, the services will not start.

From Control Panel, check the services to make sure they are started.

Ascential DataStage: Find Out What a Job Description Number Is for a Particular Job

If you take a look of the project directory, you will see something like RT_OCNFIG56 or RT_LOG56.  Each of the file reresent a job. To find out what is the job description number for a particular job, do the following:

1.Login to DataStage as root.
2.Launch the DataStage TCL prompt
3.At TCL prompt, enter “LOGTO " to log into the project account. The project name is case sensitive.

 To fine out what the job description number, enter "LIST  DS_JOBS " The job name is case sensitive. The output will look like the following:

Job name.... seqLoadAll
Description.
No.......... 746
Category.... PROJECTABC\Sales\Sequence\Main


References:

• DataStage Documentation
• Other References On The Web

Related Posts:

• Ascential DataStage: Cleanup Resources To Release Orphans Processes
• Ascential DataStage: Unlock a User's Processes and Clean Resources

Ascential DataStage: Cleanup Resources to Release Orphans Processes

When there are defunct processes with PID of 0 (zero). You can see those orphan processes (through Director) but cannot kill the processes by selecting the orphan processes and pressing "Release". You can only Unlock the process using TCL commands:

1. Login as root DataStage Server machine.
2. Launch DataStage TCL
3. At TCL prompt, you can enter "LOGTO projectname" to log into the project account. The project name is case sensitive.
4. To find out what the job description number, enter "LISTDS_JOBS jobname".
5. Enter "LIST.READU EVERY" at TCL. Check active record locks under "Item Id" column for job name or RT_CONFIG# or RT_LOG# (#matches the job description number.
6. Write down the inode number and user number for the lock that is not a valid lock.
7. Enter "LOGTO UV" to log into UV account. The UNLOCK command lives in UV account.
8. Enter "UNLOCK INODE inode# USER user# ALL". This will unlock the lock hold on this file (inode#) and hold by this user (user#) for file locks, group locks and record locks. If you need to see all the locks again, enter "LIST.READU EVERY".
9. type Q to logout from Universe

References:

• DataStage Documentations
• Other References On The Web

Related Posts:

• Ascential DataStage: Find Out What a Job Description Number Is for a Particular Job
• Ascential DataStage: Unlock a User’s Processes and Clean resources.

Ascential DataStage: Unlock a User’s Processes and Clean resources.

From time to time, if the client, i.e., the designer, was abnormally exited you will see your jobs being locked. For example, a user turns off the PC without close the datastage job first. In this situation, you need to unlock a lock on a job before you can open it.

(A.) Unlock From Unix/Linux TLC

1. Login into DataStage Server machine as root
# sudo su -root

2. Change to DataStage home directory
Type: # cd `cat /.dshome`
Alternatively, you can just type the full directory path. i.e.:
#cd /home/dsadm/Ascential/DataStage/DSEngine)

3. Source the dsenv file to set up the environment variables# . dsenv (source the dsenv file)
4. Load the TCL prompt# bin/uv you are now at the TCL prompt.
5. Type the following in the TCL to displays all the locks)

>LIST.READU EVERY

look for the lock on your job. You may need to scroll down a bit. Note the number under the userno column. You will also notice the user login id under the login id column.
6. At the TCL prompt , type the command to unlock locks for this user.

>UNLOCK USER userno ALL

Clearing Record locks.
Clearing GROUP locks.
Clearing FILE locks.

userno is the number in the userno column holding the lock.

(B.) Unlock From the DataStage Director

1. From the DataStage Director window highlight the job being accessed by another user. From the "Job" menu choose "Cleanup Resources".
2. If this is grayed out (disabled) you need to go to the DataStage Administration Window highlight this project, then choose Properties and under the General tab click on "Enable Job administration in Director".
If you had to enable "Cleanup Resources" you will need to close your Director window and log back in.
3. Once you are in the Cleanup resources Window. On both panes click on "Show All" the top pane is "processes" and the bottom pane is Locks.
Scroll down on the bottom pane until you see the job that is locked. Note the PID number. On the top pane highlight the pid number. From the bottom pane click on "Show By Process" the Release all button should now be enabled. Click on it and it should release the locks. Now try to log in an open the job in question.

References:

• DataStage Documentations
• Other References On The Web

Related Posts:

• Ascential DataStage: Find Out What a Job Description Number Is for a Particular Job
• Ascential DataStage: Cleanup Resources to Release Orphans Processes

Ascential DataStage: Configure NLS Parameter For Using Oracle Database

To ensure that DataStage will take whatever character set from the sources and place them in the target database in the same character set, you need to set up the correct environment variables in the DataStage Engine. Following are the steps for the configuration:

1. Fine out what character sets that the source and target Oracle databases are using by queering the database:
--connect to the source Oracle database
-- run the following query:
SQL> SELECT * FROM V$NLS_PARAMETERS;
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
Note the parameter values for NLS_LANGUAGE, NLS_TERRITORY and NLS_CHARACTERSET.

2. Now check the dsenv file in the DataStage server. ( It is located in … /Ascential/DataStage/DSEngine/ directory.) In the dsenv file, find the line that has the Oracle NLS_LANG variable. It should look like:
NLS_LANG=<>_<>.<>
For example: NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1; export NLS_LANG

3. Change or add this line as need. After you have edited the dsenv file, remember to stop and restart the server.

References:

• DataStage Documentations
• Other References On The Web

 Related Posts:

• Ascential DataStage: Linux Sendmail and Notification Activity
• Ascential DataStage: Routine to Reset Sequnce Key