IBM i

 View Only
Expand all | Collapse all

Azure Data Factory Studio connection to a library in the secondary ASP

  • 1.  Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Wed May 18, 2022 07:53 AM
    Hi There

    We are trying to setup a connection from the cloud,  "Azure Data Factory Studio", to access our IBMi libraries in a secondary ASP.
    The IBMi user profile has a JOBD that that has the Secondary ASP libraries in the library list, as well as the Initial ASP Group pointing to the Secondary ASP.
    The Package Collection, or the default library as we know it in the Azure Data Factory settings, is set to the library defined in the Secondary ASP. It fails with error 805, which means it cannot find the library in the secondary ASP.
    If we leave this field blank, its also fails.
    If we change it to QGPL, then it connects successfully - so we know the communication is possible.
    It appears that it cannot find the libraries in the secondary ASP.

    Has anyone had a similar problem like this or got any ideas on how to address this. There is very little info on the internet about this?

    Many thanks
    Wayne
     



    ------------------------------
    Wayne Canning
    ------------------------------


  • 2.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Wed May 18, 2022 12:10 PM

    Wayne, maybe this will help (from our documentation somewhere):

     

    IASP

    When using the lib location type and the library is located on an IASP then you will need to set the IASP id/number as the first parameter after the host for example:

    '//host[/IASP1]/QSYS.LIB/LIBRARY.LIB' or at least '/host[/IASP1]/LIBRARY.LIB', here IASP1 is a configured ASP group name.

    Note that to use an IASP in the location, the ASP must be set in the job description of the user configured for that location, for example, the following user profile has a custom job description of which the job description has the Initial ASP group configured as IASP1:


    With regards to the credentials make sure that they do not expire or change.

     

     

    Best regards,

     

    Wim Jongman, CTO

    +31(0)622371297

     

     

    remainsoftware-logo

    Embrace Change. Remain In Control.

     

    www.remainsoftware.com

    See our video to learn more about Remain.

     

           linekdin    

     

     

     






  • 3.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Thu May 19, 2022 03:15 AM
    Hi Wim

    Thanks for the response.
    Yes, we do already have the IASP details defined in a JOBD and linked to the profile as suggested.
    The connection is all defined in a GUI format, and not a command string like you mention below. I do want to try and qualify the IASP name next to the HOST name in the GUI as suggested below. Hoping that will help.

    Ill keep you posted.

    Cheers
    Wayne

    ------------------------------
    Wayne Canning
    ------------------------------



  • 4.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Thu May 19, 2022 08:44 AM
    Edited by Satid Singkorapoom Thu May 19, 2022 08:49 AM
    Dear Wayne

    Does this "secondary" ASP actually an Independent ASP (number 33 or higher)?  You can get the answer by running the command WRKDSKSTS + enter + F11 and you will see a column for "ASP".  Please scroll down until you see ASP number other than 1 and let me know what other ASP number you see there. 

    The JOBD discussed works only for "native" access, not SQL connection.  Assuming Independent ASP for now, does Azure Data Factory access the data in IBM i by sending SQL statements to it?  If so, you need to first identify an "RBD entry name" in IBM i for this Independent ASP with WRKRDBDIRE command.  (This RDB entry is created automatically when an Independent ASP is created and it should have the same name as this IASP "device name".  It has a Remote Location attribute as LOOPBACK.)

    Then you can use 3-part naming in your SQL statement. Sample   SELECT  ....   FROM   <IASP RDB ENTRY NAME>.LIB1.TABLE1


    ------------------------------
    Satid Singkorapoom
    ------------------------------



  • 5.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Mon May 23, 2022 03:13 AM
    Hello Satid

    Sorry for the delayed response.
    Just to correct my previous comments - it is actually a 2nd ASP as opposed to an IASP - Its ASP number is 33.

    Cheers
    Wayne

    ------------------------------
    Wayne Canning
    ------------------------------



  • 6.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Mon May 23, 2022 05:18 AM
    Edited by Satid Singkorapoom Mon May 23, 2022 07:46 PM
    Dear Wayne

    You have an incorrect understanding about an ASP in IBM i.  An ASP number 33 or higher is an Independent ASP (IASP).  (ASP number 2 to 32 are called User ASP). 

    When you create an IASP, its database instance name is created automatically using the same name as the IASP device description name and you will see this name in WRKRDBDIRE screen (with LOOPBACK as its location).   You need to specify this RDB entry name in the "Database name" field of the DB2 linked service set up screen for Azure as I indicated in my previous post.  Please try this and see if it solves your problem or not.

    If you want to manually submit an SQL statement through this linked service connection to Db2 for i, you can then use SQL 3-part naming.  Or you can also run the statement CONNECT TO <RDB entry name of IASP> and then you will be able to run any subsequent SQL statements to access the data residing in IASP.   

    Without this CONNECT TO or specifying Database name in OLE DB link set up or the use of SQL 3-part naming (which was available as of IBM i release 7.1). you can access only data in ASP 1 to 32 from an SQL connection.
       

    ------------------------------
    Satid Singkorapoom
    ------------------------------



  • 7.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Fri May 20, 2022 08:29 PM
    Edited by Satid Singkorapoom Fri May 20, 2022 08:40 PM
    I did more search and found that Azure uses OLE DB link to DB2 family and this is an SQL connection.  So, I do not think that JOBD method that you use would work as it does not apply to SQL connection.   In Azure's DB2 Connector set up screen below, my guess is that you should specify the RDB entry name of your IASP (seen from WRKRDBDIRE command) in the "Database Name" field.



    How large is the data size from Db2 for i do you want to import to Azure?   How regular is the import?    I ask this because I used to help a customer who imported about 200GB data from some 20 tables in DB2 for i to Azure every day and it took too long (some 4 hours) for them. It turned out the customer did not use Data Striping Set for the disk units in their Wintel server that ran Azure. You need to deploy disk Data Stripping Set for Wintel server for good disk write performance of large data size. IBM i uses data stripping set as the default feature in all its disk pools all the time (used to be called RAID-0).
       

    ------------------------------
    Satid Singkorapoom
    ------------------------------



  • 8.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Mon May 23, 2022 03:39 AM
    Hi Satid

    We installed the Microsoft OLE Provider for DB2 and as part of the installer, it has a Data Access Tool. If we configure it the same way as the data factory, it comes up with similar errors - sorry i cant upload any images, our network policies prevent that.
    The error message is as follows:
    "Connected to Data Source TEST
    The Package creation process has failed on on resource 'TEST'
    QSYS.EQNDT is an undefined name. SQLSTATE: 42704 SQLCODE: -204"


    Thanks
    Wayne




    ------------------------------
    Wayne Canning
    ------------------------------



  • 9.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted Thu May 26, 2022 02:13 PM
    When you create an iASP, the system creates it with its own database name which will be the same as the iASP name.  You can view those by using the WRKRDBDIRE or DSPRDBDIRE commands.  For your connection to work correctly, you must specify this name as the database name in your connection.  The initial ASP name in the job description is not used for this type of job (DRDA connection).  If it were, then you would have to change your job description every time you wanted to connect to *SYSBASE or the iASP which isn't a practical solution.

    ------------------------------
    Michael Swenson
    ------------------------------



  • 10.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted 30 days ago
    Hi Mike

    thanks for the feedback.
    We managed to resolve this - we had 2x RDB entries. We changed the connection string to the 2nd RDB and it worked successfully.
    But Im still puzzled why the first one is not working - they both reference the LOOPBACK.

    Cheers
    Wayne

    ------------------------------
    Wayne Canning
    ------------------------------



  • 11.  RE: Azure Data Factory Studio connection to a library in the secondary ASP

    Posted 30 days ago
    Edited by Satid Singkorapoom 30 days ago
    Dear Wayne

    >>>>  But Im still puzzled why the first one is not working - they both reference the LOOPBACK. <<<<

    What are the two RDB entry names you see ?  They should be of different names and the reason for the two entries can be that there are two IASP devices created in your system (you ask the person who created IASP for your system to confirm).  The RDB name that works for your case is the name of the active (varied on) IASP device description that your data resides.

    ------------------------------
    Satid Singkorapoom
    ------------------------------