topic how to fetch data from VSAM, DL1 and DB2 in QlikView

how to fetch data from VSAM, DL1 and DB2

Anonymous — Wed, 30 Jan 2013 09:13:20 GMT

Hello Qlik community,

I am having a difficulty trying to fetch data from three diffrent sources so please advice as per your experiences how can i fetch data from VSAM, DL1 and DB2.

Re: how to fetch data from VSAM, DL1 and DB2

Anonymous — Wed, 30 Jan 2013 12:24:44 GMT

Hi Muhammad,

You will need middleware to make a direct connection. As was previously mentioned:

- Progress DataDirect for VSAM

http://www.datadirect.com/support-data-sources/mainframe/databases/vsam.html

- Microsoft BizTalk Host Integration server

http://www.microsoft.com/biztalk/en/us/host-integration.aspx

Please be aware that you may need codepage translation (also from flat file exports of these systems) from EBCDIC into UTF-8 or ASCII.

Here's a script example of loading COBOL data:

@startpos:endpos represents the start and end positions of a field in a

file with fixed length records. The positions must both be positive integers.

The two numbers must be preceded by "@" and separated by a colon. The

numbering is always made from 1 and up to the number of positions.

If @startpos:endpos is immediately followed by the characters I or U, the

bytes read will be interpreted as a binary signed (I) or unsigned (U)

integer (Intel byte order). The number of positions read must be 1, 2 or 4.

If @startpos:endpos is immediately followed by the character R, the bytes

read will be interpreted as a binary real number (IEEE 32-bit or 64 bit

floating point). The number of positions read must be 4 or 8.

If @startpos:endpos is immediately followed by the character B, the bytes

read will be interpreted as a BCD (Binary Coded Decimal) numbers according

to the COMP-3 standard. Any number of bytes may be specified.

The codepage parameter can be used to specify that the source data is EBCDIC.

[preuba]:

LOAD RowNo() as RowId,

@1:3 as alphanumeric, // PIC X(3)

@4:7B /100 as numeric // PIC S9(5)V99 COMP-3

FROM [prueba.txt]

(fix, codepage is 500, record is 7)

;

Re: how to fetch data from VSAM, DL1 and DB2

rbecher — Wed, 30 Jan 2013 12:29:54 GMT

Hi Muhammad,

it could be possible without a middleware by using the QlikView JDBC Connector with the HXTT Cobol driver. You can take a look here:

Reading COBOL sources via JDBC directly

http://community.qlik.com/docs/DOC-2904

- Ralf

Re: how to fetch data from VSAM, DL1 and DB2

Anonymous — Wed, 30 Jan 2013 16:04:00 GMT

Ralf,

Have u tested this connector with the VSAM and pl1 as there is a difference between VSAM and cobol files.

VSAM files are files that have index structures associated with them. The indexes are embedded within the data file. one needs to access such files through an access layer to propery read/manipulate the data.

Re: how to fetch data from VSAM, DL1 and DB2

Anonymous — Wed, 30 Jan 2013 16:10:01 GMT

Thanks Bas,

But i've already looked into the Middleware you mentioned from datadirect. I will check this Microsoft Biz Talk and let you know . Also you mentioned EBCDIC file i think we currently are not working on that.

Re: how to fetch data from VSAM, DL1 and DB2

rbecher — Wed, 30 Jan 2013 16:13:04 GMT

I haven't, and maybe you're right. Btw, I' found something at IBM that might be related:

VSE Connector Client (including JDBC driver for VSAM)

http://www-03.ibm.com/systems/z/os/zvse/products/connectors.html#conn

- Ralf

Re: how to fetch data from VSAM, DL1 and DB2

rwunderlich — Wed, 30 Jan 2013 16:13:09 GMT

For DB2, you need an ODBC driver. There are a number of drivers available, from IBM, MS, DataDirect and other vendors.

For direct DL1, you'll need some middleware like the VSAM example. DL1 does not provide a relational view out of the box. You can use the IBM Federation Server and there are other vendor solutions available to create an "SQL View" of DL1 databases.

A typical approach for DL1 without middleware is to write extract programs to extract to flat files and do the QV load from there.

-Rob

Re: how to fetch data from VSAM, DL1 and DB2

Anonymous — Wed, 30 Jan 2013 17:44:31 GMT

Thanks Rob for your Advise i will surely try it.