db2_pconnect() without user/password?

General discussion on Zend Server for IBM System i
Post Reply
aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

db2_pconnect() without user/password?

Post by aseiden » Tue Aug 03, 2010 10:04 pm

I have a situation where I'd like to eliminate the need for QSQSRVR jobs. That's because the combination of db2_pconnect (persistent connect) and QSQSRVR causes read locks (SHRRD) that never expire. My customer doesn't want those endless locks, but I'd like to retain the performance advantage of db2_pconnect() if it still makes sense.

Here's what I know so far:
  • db2_pconnect() (persistent connect) allows Apache to re-use connections to QSQSRVR database jobs that Apache spawns.
  • These QSQSRVR jobs only get created when a user/password is supplied during the connect. If, on the other hand, ('', '', '') is passed in, with no user/password, database queries will be run inside the Apache jobs, not in separate QSQSRVR jobs.
My question: does db2_pconnect('', '', '') (no user/password) have any purpose? If queries are run inside the Apache jobs themselves, what am I persistently connecting to?

Thanks!

Alan Seiden

darkluke980
Posts: 61
Joined: Sat Mar 28, 2009 8:31 pm
Location: Italy
Contact:

Re: db2_pconnect() without user/password?

Post by darkluke980 » Wed Aug 04, 2010 12:13 pm

Hi,

for running query inside the Apache we modify our php.ini with this directive "ibm_db2.i5_ignore_userid=1".

Then we use db2_connect("","","").

Now, if we use db2_pconnect("","","") we hava an error "SQL0842N A connection to server server-name already exists.", because previous connection in Apache instance don't close automatically.

Bye

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2_pconnect() without user/password?

Post by aseiden » Wed Aug 04, 2010 4:01 pm

Thanks, darkluke.

Your observations confirm that db2_pconnect('', '', '') makes no sense to do. (A persistent connection with no user/password). In fact, you said you got an error when you tried it.

Since persistent connections and QSQSRVR jobs are causing an unwanted lock in my customer's system, I'll test using "ibm_db2.i5_ignore_userid=1" and db2_connect('', '', '') as you just described. If the performance is adequate then I will stick with it.

When I do this--remove the explicit user/password combo from the connection--I'll need to add the i5_libl option so that my library lists can still be set. Till now I've been using the user profile/jobd to set library lists. One thing I'll watch out for: i5_libl sets the library list by calling a stored procedure upon connecting. It's possible that the stored procedure call could affect my application's performance. I'll run some tests to determine if there's any noticeable performance hit.

Alan

darkluke980
Posts: 61
Joined: Sat Mar 28, 2009 8:31 pm
Location: Italy
Contact:

Re: db2_pconnect() without user/password?

Post by darkluke980 » Wed Aug 04, 2010 4:39 pm

Hi Alan,

in our framework, every time we make db2_connect, we add library with an RPG procedure, the running time is irrelevant.

$sql = 'CALL MYLIBL/ZDT_LIBR('LIBRE1 LIBRE2 LIBRE3 LIBRE4');
db2_exec($conn, $sql);

H*
H* CREATE PROCEDURE ZDT_LIBR
H* (IN PARM1 CHAR(600))
H* EXTERNAL NAME 'QGPL/ZDT_LIBR'
H* LANGUAGE RPGLE
H* GENERAL WITH NULLS
H*
HOPTION(*NODEBUGIO) DFTACTGRP(*NO)
C*
d CMD pr EXTPGM('QCMDEXC')
d cmd 800A OPTIONS(*VARSIZE) CONST
d cmdlen 15P 5 CONST
d $s S 600A
d $libre S 600A
c *entry plist
c parm $libre
c*
C x'00':' ' XLATE $libre $libre
c eval $s='CHGLIBL (' + %trim($libre)+')'
c callp (e) CMD($s :%size($s ))
c return

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2_pconnect() without user/password?

Post by aseiden » Wed Aug 04, 2010 4:51 pm

darkluke,

Thanks. Good to hear that your stored procedure technique doesn't add much time. Since i5_libl uses a stored procedure internally, i5_libl should work at about the same speed as your technique.

Best,
Alan

Post Reply