7 Replies Latest reply: Jan 30, 2013 12:44 PM by Muhammad Irfan RSS

    how to fetch data from VSAM, DL1 and DB2

    Muhammad Irfan

      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
          Bas van Reeuwijk

          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
            Ralf Becher

            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
              Rob Wunderlich

              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