Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

m_irfan360
Contributor

how to fetch data from VSAM, DL1 and DB2

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.

7 Replies
bvk
Contributor II

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

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)

;


MVP
MVP

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

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

m_irfan360
Contributor

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

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.

m_irfan360
Contributor

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

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.

MVP
MVP

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

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

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

m_irfan360
Contributor

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

Thanks Rob for  your Advise i will surely try it.

Community Browser