0

I have to pull data using VIEW on Oracle and insert them into one table on Oracle. Even though the source connector for the View in Data Stage can show Data by [View Data] option, ETL job fails when I run it. Target connector also responds when I click [View Data] option on the target side. However, it almost always fails to run the ETL job itself.

The connector connected to Oracle server, but it does not process rest of the ETL work.

I could populate the target table only once through the simple job flow, consisting of two Oracle connectors.

The version of connectors is 11g. I also tried 12c. Both versions show the same symptoms. Does anyone have an idea to solve this issue?

I checked out sessions along with SID, Serial # and Active Status on Oracle. It creates 9 sessions for the one ETL jobs. I'm not sure whether it's normal or not.

It should generate INSERT statement, but it started to generate INSERT statement when I stopped the ETL job by Data Stage Director.

llearner
  • 37
  • 5
  • What error message is returned? You mention that "it does not process rest of the ETL work" but on the other side it seems you "can populate it once" - please clarify – MichaelTiefenbacher Mar 22 '23 at 20:05
  • @MichaelTiefenbacher Yes, once I populated the target table through the ETL job in Data Stage. And then it couldn't finish the ETL job again since the job took too much time generating INSERT statement. I stopped running the job in DS. Hence the error message was about SIGINT meaning system interruption to terminate the job. – llearner Apr 04 '23 at 15:05

1 Answers1

1

Which connector (source or target) throws an error and what is the exact error message?

The initial post says: "The version of connectors is 11g. I also tried 12c. Both versions show the same symptoms." The version you select in the Oracle Connector should match the version of Oracle Client you are using. When you use Oracle Client 12c, 18c, 19c, or 21c, choose 12c in the Connector stage editor on BOTH connectors.

The initial post says: "I checked out sessions along with SID, Serial # and Active Status on Oracle. It creates 9 sessions for the one ETL jobs." There will be 1 session for the DataStage Conductor and one session for each player (i.e., compute node)). So, if you have 2 Oracle Connectors in your job and 4 players, that will be 10 sessions. You can reduce the number of sessions in multiple ways. For example,

  • Set user defined environment variable CC_ORA_OPTIMIZE_CONNECTIONS=YES. This means that the Oracle Connector's conductor node will drop its connection and save you a session.
  • In the Source Oracle Connector (the one that reads records), set Enable Partitioned Reads to NO.
  • Change the APT Config File to just use 1 player. The default one is located here: APT_CONFIG_FILE=/opt/IBM/InformationServer/Server/Configurations/default.apt

I hope this helps.

Emily White
  • 46
  • 2
  • Sorry for my late response. I appreciate your taking the time for the answer and the detailed information regarding the settings. The issue was resolved using Oracle Connector in Data Stage version 11g, but I still need to understand your answer and the the session information better. I'll apply the tips and knowledges to my job and will see what can be different. Thanks again. – llearner Apr 04 '23 at 15:02