Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Merged specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)

  • Nicolas Verscheure
  • Nicolas Verscheure's Avatar Topic Author
  • Offline
More
30 Oct 2014 18:17 - 13 Nov 2014 13:26 #1 by Nicolas Verscheure
specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader) was created by Nicolas Verscheure
In specific template "LOAD Rdbms to Oracle (with SQL Loader).tp", a correction is required in action "Scripting" of "Load" sub action process.
Line 2, here comes the correct code :

def command = "sqlldr control='"+ __ctx__.getVariableValue("../realPathOnServer")  + "/data_%x{$REF/tech:workName()}x%.ctl' log='"+ __ctx__.getVariableValue("../realPathOnServer")  + "/data_%x{$REF/tech:workName()}x%.log' userid=%x{$REF/ref:target()/tech:jdbcUser()}x%/%x{$REF/ref:target()/tech:jdbcPassword()}x%@%e(rhino){"%x{$REF/ref:target()/tech:jdbcUrl()}x%".replace(/^jdbc.*:@(.+):(.+):(.+)$/, "$3")}e(rhino)%"

SQL*Loader requires the name of the Oracle database instance.
Last edit: 13 Nov 2014 13:26 by Nicolas Verscheure.
More
10 Nov 2014 10:02 - 10 Nov 2014 10:02 #2 by Thomas BLETON
Replied by Thomas BLETON on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
Hi Nicolas,
The official template includes the serverName property in this expression.
Here is the end of the line :
@%x{$REF/ref:target()/tech:serverName()}x%
This expression refers to the "Physical Name" of the root database node in the metadata.

Maybe your template version does not have this expression ? Or do you think it is not correct ?
Last edit: 10 Nov 2014 10:02 by Thomas BLETON.
  • Nicolas Verscheure
  • Nicolas Verscheure's Avatar Topic Author
  • Offline
More
10 Nov 2014 17:51 - 11 Nov 2014 09:41 #3 by Nicolas Verscheure
Replied by Nicolas Verscheure on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)

Thomas BLETON wrote: Hi Nicolas,
The official template includes the serverName property in this expression.
Here is the end of the line :

@%x{$REF/ref:target()/tech:serverName()}x%
This expression refers to the "Physical Name" of the root database node in the metadata.

Maybe your template version does not have this expression ? Or do you think it is not correct ?

I have the latest template with this expression. Nevertheless Oracle SQL*Loader requires a TNS alias (Oracle SID) like SQL*Plus. Not a server name.

sqlldr userid/password@tns_alias
Last edit: 11 Nov 2014 09:41 by Nicolas Verscheure.
More
13 Nov 2014 16:32 #4 by Thomas BLETON
Replied by Thomas BLETON on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
I have two thoughts on this subject :

1. The tech:serverName() is a generic Stambia technology function which refers to the root's physical name of a metadata. Stambia abstracts technologies and sometimes the vocabulary is generic :) In the case of an Oracle metadata, the root node's Physical Name corresponds to the Oracle database SID.
Is this working fine with the way you handle oracle Metadata ? Otherwise can you give an example of why the suffix tech:serverName() would not work for sqlldr ?

2. I'm pretty sure that Oracle does not really require the @sid suffix on the sqlldr command line. It can take the default instance from the environement. In that case, I think we should avoid the suffix "@" when the database's Physical Name is empty in Metadata.
What do you think of this idea ?
  • Nicolas Verscheure
  • Nicolas Verscheure's Avatar Topic Author
  • Offline
More
13 Nov 2014 18:45 - 14 Nov 2014 16:12 #5 by Nicolas Verscheure
Replied by Nicolas Verscheure on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
1. "Physical Name" is a little bit confusing :blush:
Generally we don't fill it. When we did it, we put the physical name i.e server name. In my mind, an instance name is like a logical name.
So it was obvious for me that the field "Physical Name" corresponds to the server name. But now, with your explanations, I understand your logic.

2. You are right ! ;)
Oracle does not require the @sid suffix. But it requires a default instance. Sometimes, servers have no default instance defined in the OS environment parameters. And sometimes, there are several defined instances on the same server and you want to access to a specific one. So, I noticed that we should use your template and fill in the "Physical Name" field with the SID.

Thanks.
Last edit: 14 Nov 2014 16:12 by Nicolas Verscheure.
More
09 Sep 2016 11:13 - 09 Sep 2016 11:21 #6 by Emmanuel Rambeau
Replied by Emmanuel Rambeau on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
Good morning,

I up this topic to ask about the utility of the SQL Loader for Oracle, for our utilisation of Stambia.
I never experienced this tool before, as my team. A DBA advised us about it, but he doesn't know the functionnement of Stambia with temporary tables. That's why I ask you.

My mappings sometimes have a step of load. I think that this step could in fact be sped up by the use of SQL Loader.

Nevertheless, the step which usually take long time is the part of Integration. In fact, our data are rarely integrated directly from a source to a target. There are several joins (most often to get auto-incremented keys), with dimension table of our dataware. Hence, joins are executed in the staging area.

Below, an exemple of our mappings (which were migrated by an automatic tool, hence the use of query metadata and abusive use of stages) :



I also wonder about the utility of the SQL Loader in our case, because I don't think it can affect effectively our execution time.

What do you think about it ?Thank you in advance.

Emmanuel
Attachments:
Last edit: 09 Sep 2016 11:21 by Emmanuel Rambeau.
More
09 Sep 2016 17:04 #7 by Cyril Dussud
Replied by Cyril Dussud on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
Hi,

The Oracle SQL Loader tool is powerfull and can effectively speed up the load step.
The time you can gain with it can vary a lot depending on the amount of data you are loading.
On small datasets, you might not notice a big difference but on more large ones it can help to reduce the load times.

The downside is that you'll have to install an Oracle client on the machine where the Runtime is located so it can use the 'sqlldr' tool.
It adds a little more complexity to put it in place, but it can help if you are noticing big load times.
More
13 Sep 2016 11:09 #8 by Emmanuel Rambeau
Replied by Emmanuel Rambeau on topic specific/Rdbms/Oracle/LOAD Rdbms to Oracle (with SQL Loader)
Thank you for your answer.
I will try it.