Archives of the TeradataForum

Message Posted: Tue, 25 Sep 2012 @ 10:14:31 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Error loading data from DB2 through TPT using

From:		Ng, Edgar

Folks,

We are trying to get TPT to perform an extract from DB2 and load data to Teradata using the ODBC operator, and we have encounter the following error

     wsdr2us1@HKD03LP2502:/home/dstage/wsdr2us1 [995] $ echo $LIBPATH
     /opt/teradata/client/13.10/odbc_64/lib:/sysu/ctma/cmausr/ctm/exe:/sysp/trill
     ium/TrilliumSoftware/tsq10r0s/Software/bin:/home/sds01uc/sds01uc2/sqllib/lib 64
     wsdr2us1@HKD03LP2502:/home/dstage/wsdr2us1 [981] $ tbuild -f
     /hsbc/br2/data/charles/bcuodbc.txt wimexport1 Teradata Parallel Transporter Version 13.10.00.04 Job log:
     /opt/teradata/client/13.10/tbuild/logs/wimexport1-379.out
     Job id is wimexport1-379, running on HKD03LP2502 Found CheckPoint file:
     /opt/teradata/client/13.10/tbuild/checkpoint/wimexport1LVCP
     This is a restart job; it restarts at step MAIN_STEP.
     TPT_INFRA: TPT02192: Error: Cannot load shared library
                dlopen error:        0509-022 Cannot load module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.
             0509-150   Dependent module
     /opt/teradata/client/13.10/odbc_64/lib/libodbc.a(odbc.so) could not be loaded.
             0509-103   The module has an invalid magic number.
             0509-022 Cannot load module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.
             0509-150   Dependent module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so could not be loaded.
     TPT_INFRA: TPT02109: Cannot open shared library libodbcop.so, status = Open Error
     TPT_INFRA: TPT02240: Error: Cannot create operator object, status = Operator Error
     TPT_INFRA: TPT02103: Executor unable to set up its table object Job step MAIN_STEP
     terminated (status 8) Job wimexport1 terminated (status 8)

Have anyone come across this situation when pulling data from ODBC in TPT? We have tested on both server, an AIX box and a sandbox server with Unix installed And ended up with the same error code. The attached And our connection settings

We have then generate another test, to use TPT to perform an extract to a file from Teradata using ODBC operator and we encounter the same error, is there any additional configuration work tasks needed in order for ODBC to work on TPT on Unix and AIX? Because the exact script pulling from DB2 using TPT works when we are executing the script from our windows laptop.

Thanks,

Edgar Ng

Below is the same error that we have encountered using TPT pulling data from Teradata using ODBC operator.

     HKD03LP2541:rmptsus1:/home/dstage/rmptsus1 $ tbuild -f /home/dstage/rmptsus1/TDodbc.txt
     export1 Teradata Parallel Transporter Version 13.10.00.04 Job log:
     /opt/teradata/client/13.10/tbuild/logs/export1-44.out
     Job id is export1-44, running on HKD03LP2541 Found CheckPoint file:
     /opt/teradata/client/13.10/tbuild/checkpoint/export1LVCP
     This is a restart job; it restarts at step MAIN_STEP.
     TPT_INFRA: TPT02192: Error: Cannot load shared library
                dlopen error:        0509-022 Cannot load module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.
             0509-150   Dependent module libodbc.a(odbc.so) could not be loaded.
             0509-022 Cannot load module libodbc.a(odbc.so).
             0509-026 System error: A file or directory in the path name does not exist.
             0509-022 Cannot load module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so.
             0509-150   Dependent module
     /opt/teradata/client/13.10/tbuild/lib/libodbcop.so could not be loaded.
     TPT_INFRA: TPT02109: Cannot open shared library libodbcop.so, status = Open Error
     TPT_INFRA: TPT02240: Error: Cannot create operator object, status = Operator Error
     TPT_INFRA: TPT02103: Executor unable to set up its table object Job step MAIN_STEP
     terminated (status 8) Job export1 terminated (status 8)

is the TPT script that we've used.

     DEFINE JOB TPT_ODBC_LOAD
     (
             DEFINE OPERATOR W_1_o_export_from_odbc
             TYPE DATACONNECTOR CONSUMER
             SCHEMA *
             ATTRIBUTES
             (
                     VARCHAR FileName,
                     VARCHAR Format,
                     VARCHAR OpenMode,
                     INTEGER BlockSize,
                     INTEGER BufferSize,
                     INTEGER RetentionPeriod,
                     INTEGER RowsPerInstance,
                     INTEGER SecondarySpace,
                     INTEGER UnitCount,
                     INTEGER VigilElapsedTime,
                     INTEGER VigilWaitTime,
                     INTEGER VolumeCount,
                     VARCHAR AccessModuleName,
                     VARCHAR AccessModuleInitStr,
                     VARCHAR DirectoryPath,
                     VARCHAR ExpirationDate,
                     VARCHAR IndicatorMode,
                     VARCHAR PrimarySpace,
                     VARCHAR PrivateLogName,
                     VARCHAR RecordFormat,
                     VARCHAR RecordLength,
                     VARCHAR SpaceUnit,
                     VARCHAR TextDelimiter,
                     VARCHAR VigilNoticeFileName,
                     VARCHAR VigilStartTime,
                     VARCHAR VigilStopTime,
                     VARCHAR VolSerNumber,
                     VARCHAR UnitType
             );


     DEFINE SCHEMA W_0_s_T
     (
       EVENT_ID BIGINT,
       EVTIDKEY VARCHAR(40),
       START_DT INTDATE,
       OLD_EVENT_ID BIGINT,
       SRCE_SYS_CDE CHARACTER(6),
       UPDT_DT_TM CHARACTER(26)
     );

     DEFINE OPERATOR W_0_o_export_from_odbc
     TYPE ODBC
     SCHEMA W_0_s_T
     ATTRIBUTES
        (
          VARCHAR UserName,
          VARCHAR UserPassword,
          VARCHAR SelectStmt,
          VARCHAR PrivateLogName,
          VARCHAR DSNName,
          VARCHAR ConnectString,
          VARCHAR TruncateData
        );


     APPLY
     TO OPERATOR
     (
       W_1_o_export_from_odbc[1]

       ATTRIBUTES
       (
         FileName = '/hsbc/br2/data/charles/temp/export.txt',
         Format = 'FORMATTED',
         OpenMode = 'Write',
         IndicatorMode = 'Y',
         TextDelimiter = '|'
       )

     )
     SELECT * FROM OPERATOR
     (
       W_0_o_export_from_odbc[1]

       ATTRIBUTES
       (
         UserName = 'wsdr2us1',
         UserPassword = 'xxxxxx',
         SelectStmt = 'SELECT
         EVENT_ID,EVTIDKEY,START_DT,OLD_EVENT_ID,SRCE_SYS_CDE,UPDT_DT_TM FROM INSUM.EVTXREF fetch
         first 10 rows only;',
         DSNName = 'ODBC_BCUU03'
       )
     );
     );

our connection settings

     [ODBC Data Sources]
     DB2 Wire Protocol=DataDirect DB2 Wire Protocol Driver dBase=DataDirect dBaseFile(*.dbf)
     Informix=DataDirect Informix Informix Wire Protocol=DataDirect Informix Wire Protocol
     Driver Oracle=DataDirect Oracle Driver Oracle Wire Protocol=DataDirect Oracle Wire
     Protocol Driver SQLServer Wire Protocol=DataDirect SQL Server Wire Protocol Driver Sybase
     Wire Protocol=DataDirect Sybase Wire Protocol Driver Teradata=DataDirect Teradata
     Progress Wire Protocol=DataDirect Progress OpenEdge Wire Protocol Progress
     OpenEdge=DataDirect Progress OpenEdge Progress SQL92=DataDirect Progress SQL92
     Text=DataDirect TextFile(*.*) Driver


     [ODBC_BCUU03]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMdb223.so
     Description=DB2 V9.5
     AddStringToCreateTable=
     AlternateID=
     Database=BCUU03
     DynamicSections=100
     GrantAuthid=PUBLIC
     GrantExecute=1
     IpAddress=uat.bcun01.hk.hsbc
     IsolationLevel=CURSOR_STABILITY
     LogonID=dmg001
     Password=
     Package=BCUU03_Package
     PackageOwner=dstgadm
     TcpPort=50002
     WithHold=1
     SecurityMechanism=2


     [DB2 Wire Protocol]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMdb223.so
     Description=DataDirect DB2 Wire Protocol Driver AddStringToCreateTable= AlternateID=
     Collection=OS/390 and AS/400 (Remove for DB2 UDB)
     Database=DB2 UDB (Remove for OS/390 and AS/400) DynamicSections=100 GrantAuthid=PUBLIC
     GrantExecute=1
     IpAddress=DB2 server host
     IsolationLevel=CURSOR_STABILITY
     Location=OS/390 and AS/400 (Remove for DB2 UDB) LogonID= Password=
     Package=DB2 package name
     PackageOwner=
     TcpPort=DB2 server port
     WithHold=1

     [dBase]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMdbf23.so
     Description=DataDirect dBaseFile(*.dbf) Driver
     ApplicationUsingThreads=1
     CacheSize=4
     CreateType=dBASE5
     Database=/scratch/DataDirect_5.2/odbc/demo
     IntlSort=0
     Locking=RECORD
     UseLongNames=0
     UseLongQualifiers=
     DataFileExtension=DBF
     ExtensionCase=UPPER
     FileOpenCache=0
     LockCompatibility=dBASE

     [Informix]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMinf23.so
     Description=DataDirect Informix driver
     Database=db
     LogonID=uid
     Password=pwd
     ServerName=informixserver
     HostName=informixhost
     Service=online
     Protocol=onsoctcp
     EnableInsertCursors=0
     GetDBListFromInformix=0
     CursorBehavior=0
     CancelDetectInterval=0
     TrimBlankFromIndexName=1
     ApplicationUsingThreads=1

     [Informix Wire Protocol]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMifcl23.so
     Description=DataDirect Informix Wire Protocol driver Database=db LogonID=uid Password=pwd
     HostName=informixhost PortNumber=1500 ServerName=informixserver CancelDetectInterval=0
     TrimBlankFromIndexName=1
     ApplicationUsingThreads=1

     [Oracle]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMor823.so
     Description=DataDirect Oracle driver
     ApplicationUsingThreads=1
     ArraySize=60000
     CatalogIncludesSynonyms=1
     CatalogOptions=0
     DefaultLongDataBuffLen=1024
     DescribeAtPrepare=0
     EnableDescribeParam=0
     EnableNcharSupport=0
     EnableScrollableCursors=1
     EnableStaticCursorsForLongData=0
     EnableTimestampWithTimeZone=0
     LocalTimeZoneOffset=
     LockTimeOut=-1
     LogonID=
     OptimizeLongPerformance=0
     Password=
     ProcedureRetResults=0
     ServerName=Oracle host
     UseCurrentSchema=1

     [Oracle Wire Protocol]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMora23.so
     Description=DataDirect Oracle Wire Protocol driver
     ApplicationUsingThreads=1
     ArraySize=60000
     CachedCursorLimit=32
     CachedDescLimit=0
     CatalogIncludesSynonyms=1
     CatalogOptions=0
     DefaultLongDataBuffLen=1024
     DescribeAtPrepare=0
     EnableDescribeParam=0
     EnableNcharSupport=0
     EnableScrollableCursors=1
     EnableStaticCursorsForLongData=0
     EnableTimestampWithTimeZone=0
     HostName=Oracle server
     LocalTimeZoneOffset=
     LockTimeOut=-1
     LogonID=
     Password=
     PortNumber=Oracle server port
     ProcedureRetResults=0
     SID=Oracle SID
     UseCurrentSchema=1

     [SQLServer]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMmsss23.so
     Description=DataDirect SQL Server Wire Protocol driver Database=db LogonID=uid
     Password=pwd
     Address=sqlserverhost,1433
     QuotedId=No
     AnsiNPW=No

     [Sybase Wire Protocol]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMase23.so
     Description=DataDirect Sybase Wire Protocol Driver ApplicationName=
     ApplicationUsingThreads=1
     ArraySize=50
     Charset=
     CursorCacheSize=1
     Database=db
     DefaultLongDataBuffLen=1024
     EnableDescribeParam=0
     EnableQuotedIdentifiers=0
     InitializationString=
     Language=
     LogonID=
     NetworkAddress=Sybase host,Sybase server port
     OptimizePrepare=1
     PacketSize=0
     Password=
     RaiseErrorPositionBehavior=0
     SelectMethod=0
     WorkStationID=

     [Text]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/VMtxt23.so
     Description=DataDirect TextFile(*.*) Driver AllowUpdateAndDelete=0
     ApplicationUsingThreads=1
     CacheSize=4
     CenturyBoundary=20
     Database=/scratch/DataDirect_5.2/odbc/demo
     DataFileExtension=TXT
     DecimalSymbol=.
     Delimiter=
     FileOpenCache=0
     FirstLineNames=0
     IntlSort=0
     ScanRows=25
     TableType=Comma
     UndefinedTable=GUESS

     [Teradata]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/ivtera23.so
     Description=DataDirect Teradata
     AccountString=
     CharacterSet=ASCII
     DBCName=
     Database=
     EnableDataEncryption=No
     EnableLOBs=Yes
     EnableReconnect=No
     IntegratedSecurity=No
     LoginTimeout=20
     MapCallEscapeToExec=No
     MaxRespSize=8192
     Password=
     PortNumber=1025
     PrintOption=N
     ProcedureWithSplSource=Y
     ReportCodePageConversionErrors=0
     SecurityMechanism=
     SecurityParameter=
     ShowSelectableTables=Yes
     UserID=

     [Progress Wire Protocol]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/ivoe1023.so
     Description=DataDirect Progress OpenEdge Wire Protocol DatabaseName=
     EnableTimestampWithTimezone=1
     HostName=
     LogonID=
     Password=
     PortNumber=

     [Progress OpenEdge]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/ivpro1023.so
     Description=DataDirect Progress OpenEdge DatabaseName=
     EnableTimestampWithTimezone=1
     HostName=
     LogonID=
     Password=
     PortNumber=

     [Progress SQL92]
     Driver=/sysp/DataStage81/InformationServer/Server/branded_odbc/lib/ivpro923.so
     Description=DataDirect Progress SQL92
     DatabaseName=
     HostName=
     LogonID=
     Password=
     PortNumber=

 
 
 
 


 
  
 
  
 
  
 
 
  
<Prev 
Next> 
  
<<First 
<Prev 
Next> 
Last>>

Attachments

Library

Quick Reference