Alter session

dataservices job design
1

I want to change a parameter for oracle session before loading data.
Ex Alter session set NLS_DATE_LANGUAGE = FRENCH
What can I do this in DI

zomi (BOB member since 2008-01-14)

2

use SQL() in the script

leoyang (BOB member since 2008-02-04)

3

sql() is using a session of its own. So you would connect, alter the session and then disconnect.

You do that via environment variables, e.g. Windows

regedit: local_machine -> software -> Oracle …

Also, Oracle has options to specify the date format including the language within functions. Maybe that helps. Or you tell what your ulitmate goal with the alter session is.

Werner Daehn :de: (BOB member since 2004-12-17)

4

I know I’m digging up a thread from the grave but the topic is inline with what I’m trying to do.

I need to set an Oracle session value and have it stick throughout the job.

The particular values are:
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

The source system I’m pulling data from does not have the degree of parallelism turned on for each table and optimizer hints cannot be coded into the dataflow if using the native DI object (source table). I know I can use the SQL object to code a query with optimizer hints but the directive is that we cannot lose the metadata that is provided with the source table object and isn’t with the SQL object.

If I use the SQL() function to submit the ALTER SESSION then the settings don’t stick.

So are there any alternatives?

eganjp :us: (BOB member since 2007-09-12)

5

A DI 12 feature…

https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Database+Session+parameters

Werner Daehn :de: (BOB member since 2004-12-17)

6

Bummer. This is a production system running 11.7.2.3 and moving to 12.x is unlikely to happen any time soon. So what is “Plan B”?

eganjp :us: (BOB member since 2007-09-12)

7

You can simply change the involved tables to parallel: alter table xxxx parallel 4;

And using the parallel hint should work as well: https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Oracle+Hints+and+DI

Werner Daehn :de: (BOB member since 2004-12-17)

8

I guess I should say what is plan “E”?

A: Provide optimizer hints in SQL. DI Can’t do it.
B: Provide degrees of parallelism with ALTER SESSION. 11.x can’t do it.
C: Specify the degrees of parallelism for each table. The source data comes from a vendor system and we can’t change it.
D: Session Trigger that implements plan B. We’re currently investigating to see if this is OK with the security and dba groups.
E: ???

eganjp :us: (BOB member since 2007-09-12)

9

I’d say A) would work. My link shows how DI supports Oracle Hints via the Plan-Stability feature of the database.

Session trigger I never thought about.

Werner Daehn :de: (BOB member since 2004-12-17)

10

I think the plan stability feature would be less acceptable to the the client’s DBA group than would enabling parallelism on a table by table basis. The only thing we get to do is read data out of the source system.

eganjp :us: (BOB member since 2007-09-12)

11

Could you set up a database replica of that source system? Then you could ETL from that, and make whatever mods were necessary (like ALTER TABLE), assuming you can change a replica like that.

dnewton :us: (BOB member since 2004-01-30)

12

No, the source system is HUGE. We’re loading about 60 GB per day from it.

eganjp :us: (BOB member since 2007-09-12)