Topic
1 reply Latest Post - ‏2013-09-20T21:50:01Z by jvanboga
Cosmic_HV
Cosmic_HV
2 Posts
ACCEPTED ANSWER

Pinned topic WTX Oracle Adapter Issues

‏2013-09-17T15:23:25Z |

Hi ALL,

PFB snippet of WTX code that we run in establishing the connection to application DB and writing the data. What we see is recurring delays in file transfers which is impactng the whole business.

We are actually invoking Stored procedure from  our server to establish connection to Application DB.

=IF(Text Field:.:MQ_Message!="",

DBLOOKUP ("CALL AD_GET_LOAD_MESSAGE(" + Sequence General:.:Process + ",?)", ShortConnectString General:.:Process))

Post this we are not getting to trace on where is the delay coming from. We are unable to enable the trace as it's PRD environmnet and will impact the performance.

Stored procedure further builds thet data, control and batch file for DB operations. The source code for this SP will be maitained on the application DB end and we don't have much visibility on that.

/* Create the control file that specifies the data format of the target table. Use Run map to prevent the file from being locked*/

PUT("FILE",GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadConfigFile.ctl",

"LOAD DATA<NL>"+

"INFILE *<NL>"+

"INTO TABLE ad_load_message<NL>"+

"APPEND<NL>"+

"FIELDS TERMINATED BY ','<NL>"+

"(load_seq char,<NL>" +

" message_type CHAR,<NL>"+

" application_key CHAR,<NL>"+

" sender CHAR,<NL>"+

" send_time date ""YYYY-MM-DD HH24:MI:SS"" ""to_date(:send_time,'YYYY-MM-DD HH24:MI:SS')"",<NL>"+

" create_time sysdate,<NL>"+

" data LOBFILE (CONSTANT """+

GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process + Sequence General:Process+"_DataToLoad.txt""" +

IF(PRESENT(encoding Comp:Seq:TypeDef Logon:ElemDecl logon Comp:Seq:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process), " CHARACTERSET " + encoding Comp:Seq:TypeDef Logon:ElemDecl logon Comp:Seq:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process) + ") TERMINATED BY EOF<NL>)<NL>"+

"BEGINDATA<NL>"+

Sequence General:Process +

"," + MsgType AZHeader Field:AZHeader +

"," + AppParameters AZHeader Field:AZHeader +

"," + Source AZHeader Field:AZHeader +

"," + EITHER( FROMDATETIME(TODATETIME(TRIMLEFT(PostedDateTime AZHeader Field:AZHeader),"{D-MON-CCYY:HH24:MM: S}") ,"{CCYY-MM-DD HH24:MM:SS}"),

FROMDATETIME(TODATETIME(PostedDateTime AZHeader Field:AZHeader,"{CCYY-MM-DD HH24:MM:SS}"),"{CCYY-MM-DD HH24:MM:SS}"),

FROMDATETIME(TODATETIME(TRIMLEFT(PostedDateTime AZHeader Field:AZHeader),"{D-MON-CCYY:HH24:MM:SS}"),"{CCYY-MM-DD HH24:MM:SS}"),

FROMDATETIME(TODATETIME(TRIMLEFT(PostedDateTime AZHeader Field:AZHeader),"{D-MON-CCYY: H24:MM:SS}"),"{CCYY-MM-DD HH24:MM:SS}"),

FROMDATETIME(TODATETIME(TRIMLEFT(PostedDateTime AZHeader Field:AZHeader),"{D-MON-CCYY: H24:MM: S}"),"{CCYY-MM-DD HH24:MM:SS}"),

DATETOTEXT(CURRENTDATETIME("{CCYY-MM-DD HH24:MM:SS}")))+

"," + DATETOTEXT(CURRENTDATETIME("{CCYY-MM-DD HH24:MM:SS}")) +"<NL>") +

/* Put data to load into a file */

PUT("FILE",GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_DataToLoad.txt",Text Field:MessageData) +

/* Create the batch file that will run the Sqlldr */

PUT("FILE",GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadCommandFile.BAT",

"sqlldr " + LogOnSqlLdr General:Process + " CONTROL="+GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadConfigFile.ctl LOG="+GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadLogFile.log BAD="+GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadBadData.bad DISCARD="+GETDIRECTORY()+ database Attr:AttrList:TypeDef User Type:ElemDecl User Comp Root Element CONTROLXML:Process +Sequence General:Process+"_LoadDiscardData.dis ERRORS=9999 ROWS=2 SILENT=FEEDBACK DIRECT=TRUE<NL>")

Any further suggestions or way ahead to proceed is very much welcomed.

 

Cheers,

Hari

 

 

 

  • jvanboga
    jvanboga
    414 Posts
    ACCEPTED ANSWER

    Re: WTX Oracle Adapter Issues

    ‏2013-09-20T21:50:01Z  in response to Cosmic_HV

    I think this is the part you are concerned about.....

    =IF(Text Field:.:MQ_Message!="",

    DBLOOKUP ("CALL AD_GET_LOAD_MESSAGE(" + Sequence General:.:Process + ",?)", ShortConnectString General:.:Process))

     

    try this (assumes "ShortConnectString General:.:Process" carries your DB connection parameters)

    =IF(Text Field:.:MQ_Message!="",

    DBLOOKUP ("CALL AD_GET_LOAD_MESSAGE(" + Sequence General:.:Process + ",?)", ShortConnectString General:.:Process " -trace CALL_AD_GET_LOAD_MESSAGE.dbl"))

     

    Updated on 2013-09-20T21:50:33Z at 2013-09-20T21:50:33Z by jvanboga