| Author |
Message
|
| EricCox |
 Posted: Mon Jul 08, 2013 5:53 am Post subject: Store and Log COBOL in Oracle DB |
 |
|
Master
Joined: 08 Apr 2011
Posts: 292
|
To all,
We are currently logging request and response XML to an Oracle XMLType column. That is working nicely.
We have a new requirement to log COBOL request and response messages to Oracle.
What Oracle column datatype is the best to house the COBOL messages? And is there any example/sample snipet of how to handle that in the ESQL as we commit the data to the db via Stored Procedure?
We are on:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
"CORE 11.1.0.7.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Thanks very much as always,
EMC |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Mon Jul 08, 2013 6:02 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| EricCox |
| Posted: Mon Jul 08, 2013 6:06 am Post subject: WMB Version |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
Our version is WMB 6.0.0.9...no laughing.
We will be on WMB 8.0.0.2 by early 2014.
This might be developed in WMB 6.0.0.9 and the ported shortly to 8.0.0.2.
Thanks,
Eric |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jul 08, 2013 6:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
If you're asking for design recommendations, I would create an XML XSD that describes the COBOL structure, and store the XML payload of this COBOL structure in a CLOB.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Jul 08, 2013 6:34 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| What Oracle column datatype is the best to house the COBOL messages? |
One that can hold the type of data in the COBOL messages...
lancelotinc said:
| Quote: |
| I would create an XML XSD that describes the COBOL structure, and store the XML payload of this COBOL structure in a CLOB |
That would be a bad idea if the COBOL data contained ( or might ever contain ) packed decimals, binary integers or other non-text data.
| Quote: |
| And is there any example/sample snipet of how to handle that in the ESQL as we commit the data to the db via Stored Procedure? |
It depends entirely on what you want to do with the COBOL data before storing it. If anything. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jul 08, 2013 6:37 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| kimbert wrote: |
| Quote: |
| What Oracle column datatype is the best to house the COBOL messages? |
One that can hold the type of data in the COBOL messages...
lancelotinc said:
| Quote: |
| I would create an XML XSD that describes the COBOL structure, and store the XML payload of this COBOL structure in a CLOB |
That would be a bad idea if the COBOL data contained ( or might ever contain ) packed decimals, binary integers or other non-text data.
| Quote: |
| And is there any example/sample snipet of how to handle that in the ESQL as we commit the data to the db via Stored Procedure? |
It depends entirely on what you want to do with the COBOL data before storing it. If anything. |
As a point of clarification, the message flow that transforms the data into the XML payload is responsible for encoding-decoding any data. XML data is easier to read by dashboard programs rather than requiring the dashboard programs to encode-decode the data. The ESB is a great place to transform payload from one format to another.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| EricCox |
| Posted: Mon Jul 08, 2013 6:42 am Post subject: COBOL message |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
| The requirement is to store the native COBOL message in a column. We will decorate the row with key fields for searching and finding specific rows. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jul 08, 2013 6:47 am Post subject: Re: COBOL message |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| EricCox wrote: |
| The requirement is to store the native COBOL message in a column. We will decorate the row with key fields for searching and finding specific rows. |
The downside to this approach is that it requires the dashboard programs to be aware of your decorations and formats.
XML self-describes fields and therefore is very portable. Custom decorated fields are not portable.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| EricCox |
| Posted: Mon Jul 08, 2013 6:50 am Post subject: COBOL Field Values Stored |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
We will interogate the COBOL for fields to be used as keys to find related rows.
What is the best column datatype in Oracle to store COBOL? Yes, the COBOL is expected to hold PIC S9 type data. |
|
| Back to top |
|
|
| EricCox |
| Posted: Mon Jul 08, 2013 6:57 am Post subject: Dashboard |
|
|
Master
Joined: 08 Apr 2011
Posts: 292
|
| The dashboard program will just need to query the Oracle db to find the row(s). With the row(s) identified the dashboard will then need to select the data out of the CLOB or BLOB column to grab the entire original COBOL message from the column, save it to a file and merge it with the Copybook to inspect it and perform analysis. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jul 08, 2013 8:49 pm Post subject: Re: Dashboard |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| EricCox wrote: |
| The dashboard program will just need to query the Oracle db to find the row(s). With the row(s) identified the dashboard will then need to select the data out of the CLOB or BLOB column to grab the entire original COBOL message from the column, save it to a file and merge it with the Copybook to inspect it and perform analysis. |
There are some assumptions here:
- you will never have to search the blob or clob
- you will always save ccsid and encoding with the blob/clob
- The broker will evaluate and save the colums uses for searching
Should be doable.
The challenge here is the definition of the searcheable columns (defining the data in the blob/clob).
if the cobol is all text (pic S9(x) can be assimilated to text) I would nearly suggest that you add the XML format to your message set and save the content as XML...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
