Configuring SQL Sessions
You can add, edit, import, duplicate, and delete SQL sessions for a process.
Adding SQL Sessions
To add an SQL session for a process:
1. In the process file, go to the Configuration tab > SQL Sessions section.
2. Click 
.
.The Add SQL Session window is displayed.
3. Specify the following:
• Session Name - Type a name for the SQL session.
• Type - Select the type of connector for the SQL session.
Based on this selection, the Session Properties are displayed. Specify the values for the required properties. For more information, see Map Connectors.
• Server - Type the name of the server.
• Database - Select the database from the drop-down list or click Browse and specify the database file.
• User ID - Type the user ID to connect to the server.
• Password - Type the password to connect to the server
• Global Transaction - Select the checkbox if you want to specify the SQL session as global. It is enabled only for a few component types.
If selected, the process is transactional. This means that if a process is not completed, the entire process is rolled back.
If the process is not transactional, then most of the process was completed when the process stopped.
For example, a transformation that transforms 100 records into a SQL database uses a transactional session on the target. An error occurs on the 40th record that stops the transformation. If the process is transactional, no new records exist in the SQL database. If the session is not transactional, 40 new records exist in the SQL database.
This setting is particularly useful when a process includes multiple maps or steps in the same database session.
4. Click Test Connection to verify the connection.
An appropriate message is displayed when the test connection is successful or unsuccessful.
5. Click Save.
The SQL Session is displayed in the SQL Sessions section.
Note: Some of the session settings match those used when mapping. In such cases, the process settings override those in the transformation.
Tip... On Oracle, all Data Definition Language (DDL) statements are committed when they are executed. This includes CREATE TABLE statements. Any process step that uses a transactional session starts a new transaction if it is not already started. In Oracle terminology, this is a global transaction.
When a SQL step attempts to execute a DDL statement, Oracle wraps that statement in a separate transaction. This transaction is subordinate to the global transaction that was already started. When the statement is finished, it automatically tries to commit the subordinate transaction. This commit fails because if there is a global transaction active, it is allowed to only issue a commit. The global transaction commits the subordinate transactions. This also affects Transformation steps that have an Oracle target and use Replace mode. Replace mode causes DROP TABLE and CREATE TABLE statements to execute. If the Transformation step uses a transactional session, these statements fail.
When a SQL step attempts to execute a DDL statement, Oracle wraps that statement in a separate transaction. This transaction is subordinate to the global transaction that was already started. When the statement is finished, it automatically tries to commit the subordinate transaction. This commit fails because if there is a global transaction active, it is allowed to only issue a commit. The global transaction commits the subordinate transactions. This also affects Transformation steps that have an Oracle target and use Replace mode. Replace mode causes DROP TABLE and CREATE TABLE statements to execute. If the Transformation step uses a transactional session, these statements fail.
Editing SQL Sessions
To edit an SQL session:
1. In the process file, go to the Configuration tab > SQL Sessions section.
2. Click the SQL session that you want to edit and click 
.
.The Edit SQL Session window is displayed.
3. Modify the required fields.
For information about the fields, see Adding SQL Sessions.
4. Click Save.
The changes are saved.
Duplicating SQL Sessions
To copy an SQL session:
1. In the process file, go to the Configuration tab > SQL Sessions section.
2. Click the SQL session that you want to copy and click 
.
.The session is copied as a new session in the SQL Sessions section and the name of the new SQL session is the original name appended by "_1".
3. Click on the SQL session to edit the fields.
The Edit SQL Session window is displayed.
4. Modify the required fields.
For information about the fields, see Adding SQL Sessions.
5. Click Save.
The changes are saved.
Importing SQL Sessions
To import an SQL session:
1. In the process file, go to the Configuration tab > SQL Sessions section.
2. Click the 
.
.3. Specify the file that you want to import and click Open.
After importing a process file, all the SQL sessions present in the file are added in the SQL Sessions section.
Deleting SQL Sessions
To delete an SQL session:
1. In the process file, go to the Configuration tab > SQL Sessions section.
2. Click the SQL session that you want to delete and then click 
.
.A message asking for confirmation is displayed.
3. Click Yes.
The selected SQL session is deleted from the SQL Sessions section.
Note: If the SQL session is used in a step, then you cannot delete the SQL session.
Last modified date: 10/22/2024