Load text of variable length from MQ via TPT into Teradata

Tools

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

Tags (1)
4 REPLIES
Teradata Employee

Re: Load text of variable length from MQ via TPT into Teradata

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

Re: Load text of variable length from MQ via TPT into Teradata

message one has three characters:

abc

message two has five characters:

abcde

Thanks for your help!

Teradata Employee

Re: Load text of variable length from MQ via TPT into Teradata

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

Re: Load text of variable length from MQ via TPT into Teradata

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.