All Forums Tools
21 Aug 2012
Load text of variable length from MQ via TPT into Teradata

Hi,

I have created a MQ queue on windows that I can access from TPT and load into a teradata table.

It works as long as I place a message that is exactly 10 characters long.

In the future there will be larger xml messages placed in the queue so I need to be able to load strings of variable length(xml).

Can you please guide me on how to change my script so that it will be possible:

I paste the script I have working right now:

DEFINE JOB MQ_LOAD
DESCRIPTION 'Load a Teradata table using MQSeries'
(
DEFINE SCHEMA MQ_SCHEMA
(
 Associate_Name CHAR(10)
);

DEFINE OPERATOR MQ_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA MQ_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR FileName = 'c:\mana\test.txt',
VARCHAR Format = 'unformatted',
VARCHAR OpenMode = 'Read',
VARCHAR AccessModuleName = 'libmqs.dll',
VARCHAR AccessModuleInitStr = '-chnl test srvr 10.185.12.113 -qnm Q1 -qmgr qm.apple -TRCL 4 '
|| 'MQTRACE -CKFILE CKFILE'
);

 

DEFINE OPERATOR STREAM_OPERATOR
TYPE STREAM
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'stream_log',
VARCHAR TdpId = '10.17.27.27',
VARCHAR UserName = 'dauser',
VARCHAR UserPassword = 'dapasswd',
VARCHAR LogTable = 'targetuser.sanity_test_MQS_log',
VARCHAR ErrorTable = 'targetuser.sanity_test_MQS_error'
);
APPLY
('INSERT INTO targetuser.pop_varchar VALUES (:Associate_Name);')
TO OPERATOR (STREAM_OPERATOR[2])

SELECT * FROM OPERATOR (MQ_READER[2]);
);

Your help is very appreciated.

 

Best regards,

 

Markus Narding

 

feinholz 1234 posts Joined 05/08
22 Aug 2012

Please give an example of the data you would like to load.

And I can then help you code your script.

You can load any type of data as long as the data on the queue matches the schema (and vice versa).

So, determine the layout of the data you would like to load, and then create the schema to match.

 

--SteveF

22 Aug 2012

message one has three characters:

abc

message two has five characters:

abcde

 

Thanks for your help!

feinholz 1234 posts Joined 05/08
23 Aug 2012

The data coming through the message queue must be in "load-ready" format. This means the data must adhere to one of our supported record format definitions.

If you want the data to be character string, like you provided, then specifying the data as VARCHAR is the way to go.

However, the actual format of the data must include the preceding 2-byte field length in order for our load/unload tools to be able to process the data correctly.

 

--SteveF

24 Aug 2012

I have now succeeded in loading a message. This is how I did it.

1. Created a file by using a hexeditor.
0C 00 0A 00 61 61 61 61 61 61 61 61 61 61 0A

so what the above means is:

datalength+2 = 0C 00 = 12  
(if it would have been datalength 255 then datalength+2 would be 02 01, 2+(1*256), i think...)
datalength = 0A = 10
10 a's = 61 61 61 61 61 61 61 61 61 61
end of record marker. = 0A

2. Loaded this file into my MQ queue using rfhutil.exe found at ibm's web.
3. Loaded into teradata using TPT script:

DEFINE JOB MQ_LOAD
DESCRIPTION 'Load a Teradata table using MQSeries'
(
DEFINE SCHEMA MQ_SCHEMA
(
 Associate_Name varchar(4000)
);

DEFINE OPERATOR MQ_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA MQ_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR FileName = 'c:\mana\test.txt',
VARCHAR Format = 'formatted',
VARCHAR OpenMode = 'Read',
VARCHAR AccessModuleName = 'libmqs.dll',
VARCHAR AccessModuleInitStr = '-chnl test srvr 10.185.12.113 -qnm Q1 -qmgr qm.apple -TRCL 4 '
|| 'MQTRACE -CKFILE CKFILE'
);

 

DEFINE OPERATOR STREAM_OPERATOR
TYPE STREAM
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'stream_log',
VARCHAR TdpId = '10.11.24.27',
VARCHAR UserName = 'dauser',
VARCHAR UserPassword = 'dapassword',
VARCHAR LogTable = 'dauser.sanity_test_MQS_log',
VARCHAR ErrorTable = 'dauser.sanity_test_MQS_error'
);
APPLY
('INSERT INTO dauser.pop_varchar VALUES (:Associate_Name);')
TO OPERATOR (STREAM_OPERATOR[2])

SELECT * FROM OPERATOR (MQ_READER[2]);
);

Thanks everyone for your help.

You must sign in to leave a comment.