DB test not working

General discussion on Zend Core for IBM System i

DB test not working

Postby rschneider1 on Wed May 13, 2009 3:05 pm

Loaded core 2.6.1
Trying to test iseries DB with following
Code: Select all
<table width="75%" border="1" cellspacing="1" cellpadding="1" bgcolor="#eeeeee">
<tr> <td><b>Customer Name</b></td> <td><b>Customer Age</b></td>
<td><b>Customer City</b></td> </tr>
<?php
//Establish connection to database
$host = "<S1090C02>";
$conn = db2_connect ($host,"<RUSS>", "<russ>");
if (!$conn) :                                   
  die ("Problems connecting  to the database.");
endif;                                         
$query = "Select * from QGPL.cust";
//Execute query
$queryexe = db2_exec($conn, $query) ;
//Fetch results
print ("<b>The result of the query is:</b>\n");
while(db2_fetch_row($queryexe)) {
$custname = db2_result($queryexe, 'CUSTNAME');
$custage = db2_result($queryexe, 'CUSTAGE');
$custcity = db2_result($queryexe, 'CUSTCITY');
//Put the results in an HTML table.
print("<tr bgcolor=#ffffff>\n");
print("<td>$custname</td>\n");
print("<td>$custage</td>\n");
print("<td>$custcity</td>\n");
print("</tr>\n");
}
?>


getting problems connecting to data base error.
Subsystem is started, Host name is correct, user name and P/W are good. Is there a log I can look for problems ??
No problems on install.
thanks
russ
rschneider1
 
Posts: 3
Joined: Fri Mar 20, 2009 7:15 pm

Re: DB test not working

Postby davelhs03 on Wed May 13, 2009 3:15 pm

Whats the error Message?

Why are there "<" ">" in your host, username, and password?

should be able to connect using
$conn = db2_connect ("S1090C02","RUSS", "russ");
davelhs03
 
Posts: 38
Joined: Mon Mar 30, 2009 6:20 pm

Re: DB test not working

Postby rschneider1 on Fri May 15, 2009 2:32 pm

your right, when i took out the <> and left in quotes, it worked ....
thanks
rschneider1
 
Posts: 3
Joined: Fri Mar 20, 2009 7:15 pm

Re: DB test not working

Postby shlomov on Tue May 19, 2009 3:14 pm

WRKRDBDIRE and confirm the DB name use the php script example see article:
http://www.zend.com/support/knowledgeba ... iew_only=1
and
http://www.zend.com/support/knowledgeba ... iew_only=1

the connect string is as shown: conn = db2_connect ("DBName","USER", "password");
Shlomo Vanunu
IBM System i
User avatar
shlomov
Zend Global Support
 
Posts: 137
Joined: Mon Dec 29, 2008 2:38 pm

Re: DB test not working

Postby davelhs03 on Wed May 20, 2009 2:19 pm

shlomov,

Those links don't work. The pages are blank.
davelhs03
 
Posts: 38
Joined: Mon Mar 30, 2009 6:20 pm

Re: DB test not working

Postby scottgcampbell on Wed May 20, 2009 2:34 pm

The links work fine for me. Try a different browser maybe?
scottgcampbell
 
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: DB test not working

Postby shlomov on Wed May 20, 2009 3:05 pm

Try this link, or visit our web site and navigate from the Support to Knowledge base:
http://www.zend.com/en/support/knowledgebase.php
select the Zend for i5 products


Article 229 -
http://www.zend.com/en/support/knowledg ... iew_only=1

WRKRDBDIRE - Work with relational database directory entry

To retrieve your i5/OS Relational Database name run the i5 command work with RDB directory entry.
From an i5 command line: WRKRDBDIRE

The command allows you to show and work with one or more entries from the relational database (RDB) directory.

With this command, you can do the following with the RDB directory:
� Add new entries
� Change existing entries
� Remove entries
� Show the details of an entry
� Print an entry
� Print a list of all entries

Note: the RDB name used with db2_connect function etc.

Example:

<html>
<Head>
<Title>PHP WRKRDBDIRE </title>
</Head>
<p>PHP running WRKRDBDIRE <br>

<?php

$lsout = `/QOpenSys/usr/bin/system 'WRKRDBDIRE'`;

$line = strtok($lsout,"\n");
print "<table border CELLSPACING=2 CELLPADDING=0 BGCOLOR=\"#00FF7F\">";
print "<tr ALIGN=LEFT>";
print "<td><pre>";
print $line;
print "</pre></td>";
print "</tr>";
while ($line = strtok("\n"))
{

if (preg_match("/Relational database\s+\./", $line)) {

print "<tr>";
print "<td style=\"background-color: # FF7F00\"><pre>$line</pre></td>";
print "</tr>";
}
else {
print "<tr>";
print "<td><pre>$line</pre></td>";
print "</tr>";
}}
print "</table>";
print "the end";
?>
</body>
</html>

--------------------------------------------------------------------------------------------------------------

Article 272 -
http://www.zend.com/en/support/knowledg ... iew_only=1

DB2 Connection Basic Troubleshooting for the i5

The script is unable to connect to the i5 database using the db2_connect() function.
This article shows how to use a simple test script to validate the db2_connect() function, and shows where to find relevant error messages in the joblogs.

Troubleshooting
The most common problem we encounter is that db2_connect() requires upper case input for the database and username parameters. If there are mixed case database or username parameters in your script, please set them to all upper case, and retry the connection.

It is not necessary to put the database, username, and password into the db2_connect() statement in order to connect to the i5 database. If these are left blank, the Apache server job connects directly to the local data base, using it's own user profile. Please try executing this simple test script, which will output a list of tables in library QGPL:

<?php
$library = "QGPL";
$i5 = db2_connect("", "", "", array("i5_lib"=>"qsys2"));
$result = db2_exec($i5,
"select * from systables where table_schema = '$library'");
while ($row = db2_fetch_both($result)) {
echo $row['TABLE_NAME']."
";
}
db2_close($i5);
?>

If this test script fails, you will likely be able to see an error in one of the Apache server job logs. From the command line:

WRKSBSJOB ZEND

For each job ZENDCOREAP please display the job using option 5, then display the job log using option 10. (Don't forget to page down, there will be 8 or more ZENDCOREAP jobs). You will often find 2 errors. For instance, if you enter "xyz" for the database name in the above test script and run it, you will find these two errors:

Error occurred in SQL Call Level Interface
Relational database xyz not in relational database directory.

The first error is a common error that often appears when SQL has a problem. The second is more specific, and usually points to the cause of the problem. Position the cursor on the second error, and use F1 to display more detail:

Message ID . . . . . . : SQL0950 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 04/12/07 Time sent . . . . . . : 12:10:49

Message . . . . : Relational database xyz not in relational database
directory.
Cause . . . . . : A request for relational database xyz was made. However
the relational database name was not found in the relational database
directory.
Recovery . . . : Do one of the following:
-- Change the name of the relational database specified on the CONNECT,
SET CONNECTION, RELEASE, or DISCONNECT statement or the RDB parameter of the
SQL precompile commands.
-- Add the relational database name to the relational database directory
using the Add Relational Database Directory Entry (ADDRDBDIRE) command.

This information should be copied into your ticket when contacting Zend Support. Remember to page down if there is more information.

If the simple script example works for you, the db2_connect() function is working normally. If you only want to connect to the local data base, this is the preferred method, as it offers the best performance. However, if you need to connect using the database name, username, and password, you can modify the above test script by putting in the values for these parameters.

To find the database name for the local database on your System i, enter this on a command line:

WRKRDBDIRE

Find the entry that specifies Remote Location Name *LOCAL.

Amend the test script to use the database name, and a valid username and password. Remember that the database name and username must be in upper case:

$i5 = db2_connect("DATABASE", "USER", "password", array("i5_lib"=>"qsys2"));

Now, run the script again.

If the connection fails, the error message will appear in one of the ZENDCOREAP jobs. For instance, if we try to connect using an invalid user ID, one of the ZENDCOREAP job logs will show this pair of messages:

Error occurred in SQL Call Level Interface
Authorization failure on distributed database connection attempt.

However, if the connection is successful, the DB2 processing is handed off to a QSQSVR job in subsystem QSYSWRK. This only happens if the database name, username, and password are specified. If they are left blank, the DB2 processing is done in the ZENDCOREAP job.

We have found that on some occasions, the error messages for SQL problems are more explicit when seen in the ZENDCOREAP job log. So, if you are encountering an error when using a specified database name, and you do not see a helpful message in any of the QSQSVR jobs, try running the script with the blank defaults. Then, check the ZENDCOREAP jobs for a message, as in the prior examples.
Shlomo Vanunu
IBM System i
User avatar
shlomov
Zend Global Support
 
Posts: 137
Joined: Mon Dec 29, 2008 2:38 pm

Re: DB test not working

Postby davelhs03 on Wed May 20, 2009 7:58 pm

Yeah none of the links work... and http://www.zend.com/en/support-center/knowledge-base doesn't work when i try to search the knowledge base. Oh well, might be something on my end. Tried FF3 IE8 and Google Chrome.
davelhs03
 
Posts: 38
Joined: Mon Mar 30, 2009 6:20 pm

Re: DB test not working

Postby rcmartin on Wed Aug 03, 2011 6:14 pm

I know this is an old thread, but the described scenario now appears to be happening to me now as well, even when using the same test db2 connection script on an iSeries... which was working a few hours ago. This happened to me once before, and the only thing that fixed it was an IPL of the box.

Basically, it appears to be caused by either leaving many db2_connect() connections opened, or from most of the db2 connections running statements which ended in error. I have ended and restarted the Zend Core application server, as well as doing the same to the QSQSRVR prestart jobs in the QSYSWRK subsystem, but this has not fixed the problem. So now when running this test script, it executes fine when it uses a non-hosed QSQSRVR job, and doesn't otherwise... with the following messages logged within the ZENDCOREAP job:

AUTHORIZATION ERROR: REFER TO PTF 5761SS1 SI29733 FOR MORE INFORMATION.
Authorization failure on distributed database connection attempt.


Other than changing the PHP code to avoid db2 errors (which I have), does anyone know how to reset these QSQSRVR jobs other than IPL'g the box?

Many thanks in advance.
rcmartin
 
Posts: 15
Joined: Wed Jul 14, 2010 12:54 pm

Re: DB test not working

Postby rcmartin on Wed Aug 03, 2011 6:35 pm

As a follow-up to this post - when I use a non-blank userid and password on the db2_connect() function (and therefore not having it execute using the NOBODY profile), it works fine. So I am assuming that there is some user count limitation re: db2 connections - if so, can anyone point me to this information?
rcmartin
 
Posts: 15
Joined: Wed Jul 14, 2010 12:54 pm

Next

Return to Zend Core for i5/OS

Who is online

Users browsing this forum: No registered users and 2 guests