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

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 :


SQL*Loader requires the name of the Oracle database instance.

Hi Nicolas,
The official template includes the serverName property in this expression.
Here is the end of the line :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 ?

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.

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 ?

1. "Physical Name" is a little bit confusing
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.

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

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.

Thank you for your answer.
I will try it.