1

I was wondering, for folks familiar with DataStage, if Oracle SQLLDR can be used on DataStage. I have some sets of control files that I would like to incorporate into DataStage. A step by step way of accomplishing this will greatly be appreciated. Thanks

ema
  • 107
  • 1
  • 10
user2008558
  • 341
  • 5
  • 16
  • 1
    Would it be possible to convert from SQL*Loader to external tables instead? External tables can be run without operating system commands. – Jon Heller Jul 15 '14 at 17:31

4 Answers4

1

My guess is that you can run it with external stage in data stage. You simply put the SQLLDR command in the external stage and it will be executed. Try it and tell me what happens.

Random
  • 467
  • 1
  • 4
  • 9
1

We can use ORACLE SQL Loader in DataStage .

If you check Oracle Docs there are two types of fast loading under SQL Loader

1) Direct Path Load - less validation in database side

2) Conventional Path Load

There is less validation in Direct Load if we compare to Conventional Load.

In SQL Loader process we have to specify points like

  • Direct or not

  • Parallel or not

  • Constraint and Index options

  • Control and Discard or Log files

In DataStage , we have Oracle Enterprise and Oracle Connector Stages

Oracle Enterprise -

we have load option in this stage to load data in fast mode and we can set Environment variable OPTIONS for Oracle , example is below

OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)

Oracle Connector -

We have bulk load option for it and other properties related to SQL Loader are available in properties tab .

Example - control and discard file values all set by DataStage but you can set these properties and others manually.

Akhilesh Shivhare
  • 34
  • 3
0

As you know SQLLDR basically loads data from files to database so datastage allows you to use any input data file, that would take input in any data file like sequential file, pass them format, pass the schema of the table, and it’ll create an in memory template table, then you can use a database connecter like odbc or db2 etc. and that would load your data in your table, simple as that.

NOTE: if your table does not exist already at the backend then for first execution make it create then set it to append or truncate.

Afaq Ahmed Khan
  • 1
0

Steps:

  1. Read the data from the file(Sequential File Stage)
  2. Load it using the Oracle Connector(You could use Bulk load so that you could used direct load method using the SQL loader and the data file and control file settings can be configured manually). Bulk Load Operation: It receives records from the input link and passes them to Oracle database which formats them into blocks and appends the blocks to the target table as opposed to storing them in the available free space in the existing blocks.

You could refer the IBM documentation for more details.

Remember, there might be some restriction in loading when it comes to handling rejects, triggers or constraints when you use bulk load. It all depends on your requirement.

Benjamin
  • 1