Relational Database not found When Calling Remote Partition

Discussion in 'DB2' started by Michael Cichoski, Jan 13, 2017.

  1. Michael Cichoski

    Michael Cichoski New Member

    Hello everyone, this is my first Club Seiden post! :)

    We are receiving the following error message when executing SQL through a PHP PDO driver from one IBMi Partition connecting remotely to another IBMi Partition. "Relational database 959 []*NO not found or not available." The SQL returns data successfully, but the error is still generated with every execution. Any thoughts on how I can eliminate these errors? I have provided a basic code example and system details below.

    Code Details
    $dbServer = "WIASP1";
    $dbLib = "WEBUSER";
    $connStr = "ibm:$dbServer";
    $options = [
    PDO::ATTR_PERSISTENT => false, // also fails with Persistent enabled
    PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING, // or ERRMODE_EXCEPTION
    ];

    try {
    $conn = new PDO($connStr, null, null, $options); // uses QTMHHTTP Default user
    } catch (PDOException $e) {
    die("Connection error: ".$e->getMessage());
    }

    $query = "SELECT * FROM $dbLib.PWEBLOG";
    $stmt = $conn->prepare($query);

    if ($stmt) {
    if ($stmt->execute()) {
    if (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
    // Successful query
    var_dump($row);
    }
    }
    }


    We receive the error even if we take the library and one of our tables out of the equation. The following query also generates the same error:
    $query = "select TRIM(CHAR(HEX(GENERATE_UNIQUE()))) from sysibm.sysdummy1";

    System Details
    • IBM OS Version = 7.1
    • Zend Server Version = 8.5.3
    • PHP Version = 5.6.18
    • XML Toolkit Version = 1.9.1
    • System CCSID = 65535
    • FastCGI CCSID = 819
    • Apache CGIJobCCSID = 37
     

    Attached Files:

  2. Christian Dubé

    Christian Dubé New Member

    Have you sure "WIASP1" is available and recognized reachable host name ?
     
  3. PHPDave

    PHPDave New Member

    Looks like a connection string problem
    ```
    $connStr = "ibm:$dbServer";
    ```
    should be
    ```
    $ipaddress="40.1.2.4" // remote ip address, might not be needed
    $connStr = "ibm:DATABASE=$dbServer;HOSTNAME=$ipaddress;PORT=446;PROTOCOL=TCPIP
    ";
    ```
    change :D to colon D. The stupid emoticon is coming up
    http://php.net/manual/en/ref.pdo-ibm.connection.php
     
    Kim Mitchell likes this.
  4. Christian Dubé

    Christian Dubé New Member

    it's a good practice to wrap variable with {} inside of a variable string.

    $connStr = "ibm:{$dbServer}";
     
    Kim Mitchell likes this.
  5. Kim Mitchell

    Kim Mitchell New Member

    I work with Michael, and just saw these answers to his post.
    Yes, WIASP1 is in the list of relational database entries on the source server.
    I've changed the connection string as PHPDave and Christian suggested.....
    $ipAddress = "192.168.101.4";
    $connStr = "ibm:DATABASE={$dbServer};HOSTNAME={$ipaddress};PORT=446;PROTOCOL=TCPIP";​
    And now I'm getting a connection error:
    Connection error: SQLSTATE=42705, SQLConnect: -950 Relational database DATABASE=WIASP1;HO not in relational database directory.
    I'm off to Google the SQLSTATE & SQLConnect error codes for clues. Any other ideas will be greatly appreciated!
     
    Last edited: Mar 10, 2017
  6. Ben Roberts

    Ben Roberts New Member

    While this thread appears a few months old, I wanted to offer a solution that worked for a large internal PHP project (i.e. 100,000+ lines of code) with components intended to run on multiple operating system platforms, including Windows and IBMi (a.k.a. AS/400, iSeries, i5, etc) and access database instances on multiple database platforms.

    We were able to successfully connect to a DB2 database instance running naively on IBMi (7.1) using PHP code also running within the IBMi. This required the use of PDO and the PDO_IBM extension. The PDO_IBM extension for PHP is included and enabled by default with the PHP interpreter packaged for IBMi by Rogue Wave (i.e. Zend Server), at least with Zend Server 6 (i.e. PHP 5.6).

    The following method of configuring a PDO object allowed us to access native DB2 database tables on IBMi:

    $this->setConnection(new \PDO('ibm:'.$this->getServer(), $this->getUsername(), $this->getPassword(), [
    \PDO::ATTR_PERSISTENT => TRUE,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
    ]));

    There are a few things to bear in mind in this code snippet. The larger body of code is object-oriented and so this is a snippet from a wrapper class around all database APIs, which is why the references to '$this' appear above. The accessor method references above could be replaced with simple variable references, string literals or whatever suites the specific implementation. Also, this code is written for compatibility with PHP 5.6 and uses certain more recent language features such as name spaces, short array syntax (PHP 5.4.0+), etc. The fourth parameter to the PDO constructor using short array syntax. The appropriate IBMi machine name should be substituted for the call to the $this->getServer() method, and this identifier should be defined on the IBMi using the WRKRDBDIRE RDB(*ALL) command. See prompted command details below.

    Finally, while this allowed us to read and write data to native DB2 database tables on IBMi, it was still necessary to specify the specific library location of the table(s) with any table reference in SQL statements. We are still working to abstract this so that SQL statements can be written in a library-agnostic manner and so libraries can be determined (automatically) through configuration.

    Entry:
    Relational database . . . . . > YOUR_ID_HERE Character value
    Relational database alias . . > *NONE Character value, *NONE
    Remote location:
    Name or address . . . . . . . *LOCAL
    Type . . . . . . . . . . . . . *IP *SAME, *SNA, *IP
    Port number or service program *DRDA
    Remote authentication method:
    Preferred method . . . . . . . *USRENCPWD *SAME, *USRID, *USRIDPWD...
    Allow lower authentication . . *ALWLOWER *SAME, *ALWLOWER, *NOALWLOWER
    Encryption algorithm . . . . . . *DES *SAME, *DES, *AES
    Secure connection . . . . . . . *NONE *SAME, *NONE, *SSL
     
    Last edited: Nov 22, 2017

Share This Page