SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Error Running SQLLDR via 24x7 Scheduler

 
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite View previous topic
View next topic
Error Running SQLLDR via 24x7 Scheduler
Author Message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Error Running SQLLDR via 24x7 Scheduler Reply with quote
I am trying to troubleshoot an issue where I call Oracle sqlldr program via a batch file within a JAL script. This process recently began erroring out with the message:

"Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL"

In researching this error, it sounds like it is having trouble finding ORACLE_HOME. Can you tell me where 24x7 looks to find ORACLE_HOME? Does it look in the path? I have both the Oracle 32-bit and 64-bit client installed on Windows 2008 R2 server. I'm running the windows 24x7 version 3.4.33.

I appreciate any help with this. Thanks, Jeff
Fri Jul 01, 2011 3:55 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Your Oracle client looking for ORACLE_HOME in the registry HKEY_LOCAL_MACHINE\software\Oracle and also in envoronment variables looking for ORACLE_HOME variable
Fri Jul 01, 2011 4:05 pm View user's profile Send private message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Reply with quote
There is no ORACLE_HOME environment variable. However under the HKEY_LOCAL_MACHINE\Software\Oracle there are several different ORACLE_HOME keys. This server has other Oracle application software on it is the reason there are so many entries in this registry section. In addition the 32-bit Oracle client registry entry has an ORACLE_HOME under the HKEY_LOCAL_MACHINE\Software\Wow6432Node\Oracle.

With all these ORACLE_HOME entries, I'm wondering how 24x7 picks the correct one? I can upload screenshots of the registry if that would help.

Thanks,
Jeff
Fri Jul 01, 2011 4:40 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
It does not. It simply executes the sqlldr command you specified in the JAL script. What happens after that depends on your Oracle client(s) installation. Try executing the same command from DOS window from24x7 installation folder.
Fri Jul 01, 2011 5:02 pm View user's profile Send private message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Reply with quote
It seems to run fine from the dos command in the 24x7 directory. One thing that I've been able to consistently reproduce is if I have a job that executes some sql from JAL prior to calling the sqlldr portion like the one below. I will get the error every time on the sqlldr section. However, if I re-start 24x7 and comment out the sql portion and just run the sqlldr it will work fine. There seems to be a problem when they are combined.


DIM processid, number
DIM sql, string

DatabaseConnect "edwp"
ConcatEx "Delete From fab_capex_data Where 1=2", sql
DatabaseExecute( sql, processid )
DatabaseDisconnect( )

//Run sqlldr command
RunAndWait( "e:\\transfer\\fab\\scripts\\fab_capex_data_curr.bat", "", 0 , processid )

Fri Jul 01, 2011 5:32 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Try setting JAL job to run as a detached job. If you have both 32-bit and 64-bit oracle clients installed, the jobs may conflict
Fri Jul 01, 2011 5:49 pm View user's profile Send private message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Reply with quote
Unfortunately, it is already set as detached.
Fri Jul 01, 2011 5:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
The other option is to try launching sqlldr using Windows start command and specifying /I switch "START /I sqlldr CONTROL= …etc…"
Tue Jul 05, 2011 1:39 pm View user's profile Send private message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Reply with quote
I tried adding the START /I to the batch file that runs sqlldr. It still errors out if the script includes a Databaseconnect in it. I have isolated it even further in testing, no sql command needs to be ran to cause the error, only a databaseconnect. See below:

This Script Fails:
DIM processid, number
Databaseconnect "edwp"
Databasedisconnect ()
RunAndWait("C:\appData\Data\Transfer\FAB\scripts\fab_incstmt_data_curr.bat", "", 0 , processid )


This Script works:
DIM processid, number
//Databaseconnect "edwp"
//Databasedisconnect ()
RunAndWait("C:\appData\Data\Transfer\FAB\scripts\fab_incstmt_data_curr.bat", "", 0 , processid )

Tue Jul 05, 2011 2:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
I'm afraid that might be specific to your Oracle configuration and multiple Oracle clients and homes. DatabaseConnect in 24x7 JAL job loads Oracle drivers into the memory and establishes connection to the database. Basically it loads OCI.DLL, whichever is found first in the path, and that DLL in turn loads Oracle client. OCI.DLL is part of Oracle install. What happens after that and what happens between Oracle client (OCI.DLL) and sqlldr we don't know.

If you have multiple copies of sqlldr, try calling other copies. To see which OCI.DLL is first in part, from DOS command window run PATH command, check directory list the PATH output and find the DLL. I guess sqllds needs to be called from the same Oracle home.
Tue Jul 05, 2011 3:39 pm View user's profile Send private message
jeff33190



Joined: 22 Aug 2007
Posts: 96

Post Reply with quote
I think I have found a solution to my problem. The database connection I was using "edwp" was based on a database profile that was created under the "Oracle 8.0.4 - 8i and later" drivers that ship with 24x7.

I created a new ODBC connection pointing to my 32-bit Oracle client driver. When I use the new ODBC database profile in the script, then sqlldr runs fine.

Thanks for your help.
Tue Jul 05, 2011 6:09 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.