Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ASE 15.0.3 preparedStatement/ProcedureCache question

15 posts in General Discussion Last posting was on 2010-07-13 07:21:33.0Z
Simon Posted on 2010-07-08 15:36:44.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: ASE 15.0.3 preparedStatement/ProcedureCache question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c35f08c$1@forums-1-dub>
Date: 8 Jul 2010 08:36:44 -0700
X-Trace: forums-1-dub 1278603404 10.22.241.152 (8 Jul 2010 08:36:44 -0700)
X-Original-Trace: 8 Jul 2010 08:36:44 -0700, vip152.sybase.com
Lines: 101
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29343
Article PK: 78578

ASE Environment:
-->Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS
5.8/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009
-->Solaris10

Hi,

I'm discovering preparedStatement.As far as I understand, one
interesting point about preparedStatement is it's a precompiled and ASE
will compile a 'lightweigh proc' for it and reuse it everytime the SQL
is called.

I'm now executing the same java sample code for several time and just
notice that ASE keeps creating a proc for it. Isn't it supposed to use a
previous LWP?

Here's a sample of my java code (based on a sample code I got from
jConnect for JDBC 7.0 doco)

Properties props = new Properties();
props.put("user", "SIMONM");
props.put("password", "xxx");
props.put("applicationname","simon");
props.put("DYNAMIC_PREPARE","true");
props.put("LITERAL_PARAMS","false");

DriverManager.registerDriver(
(Driver) Class.forName(
"com.sybase.jdbc3.jdbc.SybDriver").newInstance());
Connection con = DriverManager.getConnection(
"jdbc:sybase:Tds:host:ip", props);
/*
* Create a statement object, the container for the SQL
* statement. May throw a SQLException.
*/
//Statement stmt = con.createStatement();
PreparedStatement stmt = con.prepareStatement("select
XACT_NUM, XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
stmt.setInt(1,18819241);
/*
* Create a result set object by executing the query.
* May throw a SQLException.
*/
ResultSet rs = stmt.executeQuery();


Now I execute it several time under Eclipse. Then I put some breakpoints
in order to trace the SPID created at the next execution.

On the UNIX server I execute the following commands:
set tracefile "/tmp/simon.log" for xxx
go
set show_sqltext on
go

After the end of the tracing, here's the content of my log file

================================================================================
Application Tracing report for spid -1 from application "simon"
run by login "SIMONM" on host "EMEA-STC-LTD040"
Tracing started on 2010/07/08 17:25:29.54
================================================================================

2010/07/08 17:25:41.43
SQL Text: create proc dyn100 as select XACT_NUM, XACT_DAT, INST_NUM,
PF_COD from xxx..ID_XACT where XACT_NUM = ?

Sproc: sp_sql_type_name, Line: 0

Sproc: sp_sql_type_name, Line: 18

Sproc: sp_sql_type_name, Line: 20

Sproc: sp_sql_type_name, Line: 23

Sproc: sp_sql_type_name, Line: 33

Sproc: sp_sql_type_name, Line: 35

Sproc: sp_sql_type_name, Line: 57

Sproc: sp_sql_type_name, Line: 65

Sproc: sp_sql_type_name, Line: 75

Sproc: sp_sql_type_name, Line: 0

Sproc: sp_sql_type_name, Line: 0

2010/07/08 17:25:43.47
No useful sqltext available.

Prepared Statement: dyn100, SQL Text: select XACT_NUM, XACT_DAT,
INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?End of Batch 5

2010/07/08 17:25:44.88
End of Batch 6

-->So my question is: Why did ASE recreate a LWP?

Thanks to all for your hints


Cory Sane [TeamSybase] Posted on 2010-07-08 22:53:19.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4c35f08c$1@forums-1-dub>
In-Reply-To: <4c35f08c$1@forums-1-dub>
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Lines: 122
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18197
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3656df$1@forums-1-dub>
Date: 8 Jul 2010 15:53:19 -0700
X-Trace: forums-1-dub 1278629599 10.22.241.152 (8 Jul 2010 15:53:19 -0700)
X-Original-Trace: 8 Jul 2010 15:53:19 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29346
Article PK: 78576

Simon,
I'm a DBA that discovered that my development team was having the same issue with PERL prepared statements in ASE.

If the results set was not drained from the previous execusion , the process would not reuse the connection then doing the
following:

[Loop]
Create ASE connection
Create a prepared statement
exec prepared statement
drop connection (and prepared statement)
[End loop]

This happened 40,000 an hour for every time the query was ran...

The development team did not believe me until I showed them the sybsecurity login audits with cmdtext and a few comments about
prepared statements...

I understood at the time that JAVA can have the same behavior.



--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Simon" <sst.misc@gmail.com> wrote in message news:4c35f08c$1@forums-1-dub...
> ASE Environment:
> -->Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS 5.8/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009
> -->Solaris10
>
> Hi,
>
> I'm discovering preparedStatement.As far as I understand, one interesting point about preparedStatement is it's a precompiled
> and ASE will compile a 'lightweigh proc' for it and reuse it everytime the SQL is called.
>
> I'm now executing the same java sample code for several time and just notice that ASE keeps creating a proc for it. Isn't it
> supposed to use a previous LWP?
>
> Here's a sample of my java code (based on a sample code I got from jConnect for JDBC 7.0 doco)
>
> Properties props = new Properties();
> props.put("user", "SIMONM");
> props.put("password", "xxx");
> props.put("applicationname","simon");
> props.put("DYNAMIC_PREPARE","true");
> props.put("LITERAL_PARAMS","false");
> DriverManager.registerDriver(
> (Driver) Class.forName(
> "com.sybase.jdbc3.jdbc.SybDriver").newInstance());
> Connection con = DriverManager.getConnection(
> "jdbc:sybase:Tds:host:ip", props);
> /*
> * Create a statement object, the container for the SQL
> * statement. May throw a SQLException.
> */
> //Statement stmt = con.createStatement();
> PreparedStatement stmt = con.prepareStatement("select XACT_NUM, XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where
> XACT_NUM = ?");
> stmt.setInt(1,18819241);
> /*
> * Create a result set object by executing the query.
> * May throw a SQLException.
> */
> ResultSet rs = stmt.executeQuery();
>
>
> Now I execute it several time under Eclipse. Then I put some breakpoints in order to trace the SPID created at the next
> execution.
>
> On the UNIX server I execute the following commands:
> set tracefile "/tmp/simon.log" for xxx
> go
> set show_sqltext on
> go
>
> After the end of the tracing, here's the content of my log file
>
> ================================================================================
> Application Tracing report for spid -1 from application "simon"
> run by login "SIMONM" on host "EMEA-STC-LTD040"
> Tracing started on 2010/07/08 17:25:29.54
> ================================================================================
>
> 2010/07/08 17:25:41.43
> SQL Text: create proc dyn100 as select XACT_NUM, XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?
>
> Sproc: sp_sql_type_name, Line: 0
>
> Sproc: sp_sql_type_name, Line: 18
>
> Sproc: sp_sql_type_name, Line: 20
>
> Sproc: sp_sql_type_name, Line: 23
>
> Sproc: sp_sql_type_name, Line: 33
>
> Sproc: sp_sql_type_name, Line: 35
>
> Sproc: sp_sql_type_name, Line: 57
>
> Sproc: sp_sql_type_name, Line: 65
>
> Sproc: sp_sql_type_name, Line: 75
>
> Sproc: sp_sql_type_name, Line: 0
>
> Sproc: sp_sql_type_name, Line: 0
>
> 2010/07/08 17:25:43.47
> No useful sqltext available.
>
> Prepared Statement: dyn100, SQL Text: select XACT_NUM, XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?End of
> Batch 5
>
> 2010/07/08 17:25:44.88
> End of Batch 6
>
> -->So my question is: Why did ASE recreate a LWP?
>
> Thanks to all for your hints


Simon Posted on 2010-07-09 08:17:16.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub>
In-Reply-To: <4c3656df$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c36db0c$1@forums-1-dub>
Date: 9 Jul 2010 01:17:16 -0700
X-Trace: forums-1-dub 1278663436 10.22.241.152 (9 Jul 2010 01:17:16 -0700)
X-Original-Trace: 9 Jul 2010 01:17:16 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29348
Article PK: 78581

Le 09/07/2010 00:53, Cory Sane [TeamSybase] a écrit :

> Simon,
> I'm a DBA that discovered that my development team was having the same
> issue with PERL prepared statements in ASE.
>
> If the results set was not drained from the previous execusion , the
> process would not reuse the connection then doing the following:
>
> [Loop]
> Create ASE connection
> Create a prepared statement
> exec prepared statement
> drop connection (and prepared statement)
> [End loop]
>
> This happened 40,000 an hour for every time the query was ran...
>
> The development team did not believe me until I showed them the
> sybsecurity login audits with cmdtext and a few comments about prepared
> statements...
>
> I understood at the time that JAVA can have the same behavior.
>
>
>

Hi Cory,

In my case, I'm sure to return only one row. So I guess at the end of my
execution my resultSet is drained/emptied.
I'm interested in how you show the development team this behaviour. How
did you do that, did you just activated the AUDIT on the DB?

Thanks.


Simon Posted on 2010-07-09 10:23:38.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub>
In-Reply-To: <4c3656df$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c36f8aa$1@forums-1-dub>
Date: 9 Jul 2010 03:23:38 -0700
X-Trace: forums-1-dub 1278671018 10.22.241.152 (9 Jul 2010 03:23:38 -0700)
X-Original-Trace: 9 Jul 2010 03:23:38 -0700, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29349
Article PK: 78582

Le 09/07/2010 00:53, Cory Sane [TeamSybase] a écrit :

> Simon,
> I'm a DBA that discovered that my development team was having the same
> issue with PERL prepared statements in ASE.
>
> If the results set was not drained from the previous execusion , the
> process would not reuse the connection then doing the following:
>
> [Loop]
> Create ASE connection
> Create a prepared statement
> exec prepared statement
> drop connection (and prepared statement)
> [End loop]
>
> This happened 40,000 an hour for every time the query was ran...
>
> The development team did not believe me until I showed them the
> sybsecurity login audits with cmdtext and a few comments about prepared
> statements...
>
> I understood at the time that JAVA can have the same behavior.
>
>
>

Hi Cory,

Maybe you can help me on interpreting the AUDIT trace I enable while
replaying my test (I'll just keep interesting info).

event,eventmod,spid,loginname,dbname,objname,extrainfo
17,1,219,'SIMONM','KBC',,'; USE CMD; ; ; ; ; SIMONM/ase; '
37,2,219,'SIMONM','KBC',,'; ; ; ; 4017.16.1; ; SIMONM/ase; '
45,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040, 10.244.140.179,
network password no encryption ; ; SIMONM/ase; '
38,1,219,'SIMONM','sybsystemprocs','sp_mda','; ; ; ; 1, 7; ; ; '
17,1,219,'SIMONM','master',,'; OUTSIDE REFERENCE; ; ; ; ; SIMONM/ase; '
62,1,219,'SIMONM','master','spt_mda','; SELECT; ; ; ; ; SIMONM/ase; '
92,1,219,'SIMONM','KBC',,'set quoted_identifier on set textsize 2147483647'
92,1,219,'SIMONM','KBC',,'create proc dyn100 as select XACT_NUM,
XACT_DAT, INST_NUM, PF_COD from KBC..ID_XACT where XACT_NUM = ?'
38,1,219,'SIMONM','sybsystemprocs','sp_sql_type_name','; ; ; ; 108, 0; ; ; '
17,1,219,'SIMONM','sybsystemprocs',,'; OUTSIDE REFERENCE; ; ; ; ;
SIMONM/ase; '
62,1,219,'SIMONM','sybsystemprocs','spt_jdbc_datatype_info','; SELECT; ;
; ; ; SIMONM/ase; '
38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ; '
62,1,219,'SIMONM','KBC','ID_XACT','; SELECT; ; ; ; ; SIMONM/ase; '
46,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040; ; SIMONM/ase; '

Here are my questions:
-line 3 is event 37=error but how can I get more info about it? what's
the value 4017.16.1
-line 5-->7, what is this?
-line 8 is my proc creation?
-line 38 is an exec but what's sp_sql_type_name

I guess the line
38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ; '
is my proc execution as I recognized my args and *00021900000000_789ca0
must be my proc id

Basically what are the other lines explanation?

Thanks for your help?


Carl Kayser Posted on 2010-07-09 15:46:51.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub> <4c36f8aa$1@forums-1-dub>
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Lines: 78
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c37446b$1@forums-1-dub>
Date: 9 Jul 2010 08:46:51 -0700
X-Trace: forums-1-dub 1278690411 10.22.241.152 (9 Jul 2010 08:46:51 -0700)
X-Original-Trace: 9 Jul 2010 08:46:51 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29350
Article PK: 78587


"Simon" <sst.misc@gmail.com> wrote in message
news:4c36f8aa$1@forums-1-dub...
> Le 09/07/2010 00:53, Cory Sane [TeamSybase] a écrit :
>> Simon,
>> I'm a DBA that discovered that my development team was having the same
>> issue with PERL prepared statements in ASE.
>>
>> If the results set was not drained from the previous execusion , the
>> process would not reuse the connection then doing the following:
>>
>> [Loop]
>> Create ASE connection
>> Create a prepared statement
>> exec prepared statement
>> drop connection (and prepared statement)
>> [End loop]
>>
>> This happened 40,000 an hour for every time the query was ran...
>>
>> The development team did not believe me until I showed them the
>> sybsecurity login audits with cmdtext and a few comments about prepared
>> statements...
>>
>> I understood at the time that JAVA can have the same behavior.
>>
>>
>>
> Hi Cory,
>
> Maybe you can help me on interpreting the AUDIT trace I enable while
> replaying my test (I'll just keep interesting info).
>
> event,eventmod,spid,loginname,dbname,objname,extrainfo
> 17,1,219,'SIMONM','KBC',,'; USE CMD; ; ; ; ; SIMONM/ase; '
> 37,2,219,'SIMONM','KBC',,'; ; ; ; 4017.16.1; ; SIMONM/ase; '
> 45,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040, 10.244.140.179, network
> password no encryption ; ; SIMONM/ase; '
> 38,1,219,'SIMONM','sybsystemprocs','sp_mda','; ; ; ; 1, 7; ; ; '
> 17,1,219,'SIMONM','master',,'; OUTSIDE REFERENCE; ; ; ; ; SIMONM/ase; '
> 62,1,219,'SIMONM','master','spt_mda','; SELECT; ; ; ; ; SIMONM/ase; '
> 92,1,219,'SIMONM','KBC',,'set quoted_identifier on set textsize
> 2147483647'
> 92,1,219,'SIMONM','KBC',,'create proc dyn100 as select XACT_NUM, XACT_DAT,
> INST_NUM, PF_COD from KBC..ID_XACT where XACT_NUM = ?'
> 38,1,219,'SIMONM','sybsystemprocs','sp_sql_type_name','; ; ; ; 108, 0; ; ;
> '
> 17,1,219,'SIMONM','sybsystemprocs',,'; OUTSIDE REFERENCE; ; ; ; ;
> SIMONM/ase; '
> 62,1,219,'SIMONM','sybsystemprocs','spt_jdbc_datatype_info','; SELECT; ; ;
> ; ; SIMONM/ase; '
> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
> '
> 62,1,219,'SIMONM','KBC','ID_XACT','; SELECT; ; ; ; ; SIMONM/ase; '
> 46,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040; ; SIMONM/ase; '
>
> Here are my questions:
> -line 3 is event 37=error but how can I get more info about it? what's the
> value 4017.16.1
> -line 5-->7, what is this?
> -line 8 is my proc creation?
> -line 38 is an exec but what's sp_sql_type_name
>
> I guess the line
> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
> '
> is my proc execution as I recognized my args and *00021900000000_789ca0
> must be my proc id
>
> Basically what are the other lines explanation?
>
> Thanks for your help?

You probably want to look at pages 668-678 at
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.5/title.htm.


Simon Posted on 2010-07-09 16:01:51.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub> <4c36f8aa$1@forums-1-dub> <4c37446b$1@forums-1-dub>
In-Reply-To: <4c37446b$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3747ef@forums-1-dub>
Date: 9 Jul 2010 09:01:51 -0700
X-Trace: forums-1-dub 1278691311 10.22.241.152 (9 Jul 2010 09:01:51 -0700)
X-Original-Trace: 9 Jul 2010 09:01:51 -0700, vip152.sybase.com
Lines: 81
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29351
Article PK: 78585

Le 09/07/2010 17:46, Carl Kayser a écrit :

> "Simon"<sst.misc@gmail.com> wrote in message
> news:4c36f8aa$1@forums-1-dub...
>> Le 09/07/2010 00:53, Cory Sane [TeamSybase] a écrit :
>>> Simon,
>>> I'm a DBA that discovered that my development team was having the same
>>> issue with PERL prepared statements in ASE.
>>>
>>> If the results set was not drained from the previous execusion , the
>>> process would not reuse the connection then doing the following:
>>>
>>> [Loop]
>>> Create ASE connection
>>> Create a prepared statement
>>> exec prepared statement
>>> drop connection (and prepared statement)
>>> [End loop]
>>>
>>> This happened 40,000 an hour for every time the query was ran...
>>>
>>> The development team did not believe me until I showed them the
>>> sybsecurity login audits with cmdtext and a few comments about prepared
>>> statements...
>>>
>>> I understood at the time that JAVA can have the same behavior.
>>>
>>>
>>>
>> Hi Cory,
>>
>> Maybe you can help me on interpreting the AUDIT trace I enable while
>> replaying my test (I'll just keep interesting info).
>>
>> event,eventmod,spid,loginname,dbname,objname,extrainfo
>> 17,1,219,'SIMONM','KBC',,'; USE CMD; ; ; ; ; SIMONM/ase; '
>> 37,2,219,'SIMONM','KBC',,'; ; ; ; 4017.16.1; ; SIMONM/ase; '
>> 45,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040, 10.244.140.179, network
>> password no encryption ; ; SIMONM/ase; '
>> 38,1,219,'SIMONM','sybsystemprocs','sp_mda','; ; ; ; 1, 7; ; ; '
>> 17,1,219,'SIMONM','master',,'; OUTSIDE REFERENCE; ; ; ; ; SIMONM/ase; '
>> 62,1,219,'SIMONM','master','spt_mda','; SELECT; ; ; ; ; SIMONM/ase; '
>> 92,1,219,'SIMONM','KBC',,'set quoted_identifier on set textsize
>> 2147483647'
>> 92,1,219,'SIMONM','KBC',,'create proc dyn100 as select XACT_NUM, XACT_DAT,
>> INST_NUM, PF_COD from KBC..ID_XACT where XACT_NUM = ?'
>> 38,1,219,'SIMONM','sybsystemprocs','sp_sql_type_name','; ; ; ; 108, 0; ; ;
>> '
>> 17,1,219,'SIMONM','sybsystemprocs',,'; OUTSIDE REFERENCE; ; ; ; ;
>> SIMONM/ase; '
>> 62,1,219,'SIMONM','sybsystemprocs','spt_jdbc_datatype_info','; SELECT; ; ;
>> ; ; SIMONM/ase; '
>> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
>> '
>> 62,1,219,'SIMONM','KBC','ID_XACT','; SELECT; ; ; ; ; SIMONM/ase; '
>> 46,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040; ; SIMONM/ase; '
>>
>> Here are my questions:
>> -line 3 is event 37=error but how can I get more info about it? what's the
>> value 4017.16.1
>> -line 5-->7, what is this?
>> -line 8 is my proc creation?
>> -line 38 is an exec but what's sp_sql_type_name
>>
>> I guess the line
>> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
>> '
>> is my proc execution as I recognized my args and *00021900000000_789ca0
>> must be my proc id
>>
>> Basically what are the other lines explanation?
>>
>> Thanks for your help?
>
>
> You probably want to look at pages 668-678 at
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.5/title.htm.
>
>

Thank you Cory, I'll read it thoroughly.

Simon


Cory Sane [TeamSybase] Posted on 2010-07-11 15:28:59.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub> <4c36f8aa$1@forums-1-dub> <4c37446b$1@forums-1-dub> <4c3747ef@forums-1-dub>
In-Reply-To: <4c3747ef@forums-1-dub>
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Lines: 98
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18197
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c39e33b@forums-1-dub>
Date: 11 Jul 2010 08:28:59 -0700
X-Trace: forums-1-dub 1278862139 10.22.241.152 (11 Jul 2010 08:28:59 -0700)
X-Original-Trace: 11 Jul 2010 08:28:59 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29352
Article PK: 78583

I was always looking for events 45 & 92(cmdtxt with "create proc dyn)" & 46.
If in every case you do the following then something is wrong:
login(45),
create proc(92+"create proc dyn"),
execute it(38 sometimes hard to see)
logout(46)

My development team really had to play with their code to realize what they were doing.
They never thought they were spawning new connections for every command.


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Simon" <sst.misc@gmail.com> wrote in message news:4c3747ef@forums-1-dub...
> Le 09/07/2010 17:46, Carl Kayser a écrit :
>> "Simon"<sst.misc@gmail.com> wrote in message
>> news:4c36f8aa$1@forums-1-dub...
>>> Le 09/07/2010 00:53, Cory Sane [TeamSybase] a écrit :
>>>> Simon,
>>>> I'm a DBA that discovered that my development team was having the same
>>>> issue with PERL prepared statements in ASE.
>>>>
>>>> If the results set was not drained from the previous execusion , the
>>>> process would not reuse the connection then doing the following:
>>>>
>>>> [Loop]
>>>> Create ASE connection
>>>> Create a prepared statement
>>>> exec prepared statement
>>>> drop connection (and prepared statement)
>>>> [End loop]
>>>>
>>>> This happened 40,000 an hour for every time the query was ran...
>>>>
>>>> The development team did not believe me until I showed them the
>>>> sybsecurity login audits with cmdtext and a few comments about prepared
>>>> statements...
>>>>
>>>> I understood at the time that JAVA can have the same behavior.
>>>>
>>>>
>>>>
>>> Hi Cory,
>>>
>>> Maybe you can help me on interpreting the AUDIT trace I enable while
>>> replaying my test (I'll just keep interesting info).
>>>
>>> event,eventmod,spid,loginname,dbname,objname,extrainfo
>>> 17,1,219,'SIMONM','KBC',,'; USE CMD; ; ; ; ; SIMONM/ase; '
>>> 37,2,219,'SIMONM','KBC',,'; ; ; ; 4017.16.1; ; SIMONM/ase; '
>>> 45,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040, 10.244.140.179, network
>>> password no encryption ; ; SIMONM/ase; '
>>> 38,1,219,'SIMONM','sybsystemprocs','sp_mda','; ; ; ; 1, 7; ; ; '
>>> 17,1,219,'SIMONM','master',,'; OUTSIDE REFERENCE; ; ; ; ; SIMONM/ase; '
>>> 62,1,219,'SIMONM','master','spt_mda','; SELECT; ; ; ; ; SIMONM/ase; '
>>> 92,1,219,'SIMONM','KBC',,'set quoted_identifier on set textsize
>>> 2147483647'
>>> 92,1,219,'SIMONM','KBC',,'create proc dyn100 as select XACT_NUM, XACT_DAT,
>>> INST_NUM, PF_COD from KBC..ID_XACT where XACT_NUM = ?'
>>> 38,1,219,'SIMONM','sybsystemprocs','sp_sql_type_name','; ; ; ; 108, 0; ; ;
>>> '
>>> 17,1,219,'SIMONM','sybsystemprocs',,'; OUTSIDE REFERENCE; ; ; ; ;
>>> SIMONM/ase; '
>>> 62,1,219,'SIMONM','sybsystemprocs','spt_jdbc_datatype_info','; SELECT; ; ;
>>> ; ; SIMONM/ase; '
>>> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
>>> '
>>> 62,1,219,'SIMONM','KBC','ID_XACT','; SELECT; ; ; ; ; SIMONM/ase; '
>>> 46,1,219,'SIMONM','KBC',,'; ; ; ; EMEA-STC-LTD040; ; SIMONM/ase; '
>>>
>>> Here are my questions:
>>> -line 3 is event 37=error but how can I get more info about it? what's the
>>> value 4017.16.1
>>> -line 5-->7, what is this?
>>> -line 8 is my proc creation?
>>> -line 38 is an exec but what's sp_sql_type_name
>>>
>>> I guess the line
>>> 38,1,219,'SIMONM','tempdb','*00021900000000_789ca0','; ; ; ; 18819241; ; ;
>>> '
>>> is my proc execution as I recognized my args and *00021900000000_789ca0
>>> must be my proc id
>>>
>>> Basically what are the other lines explanation?
>>>
>>> Thanks for your help?
>>
>>
>> You probably want to look at pages 668-678 at
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.help.ase.15.5/title.htm.
>>
>>
> Thank you Cory, I'll read it thoroughly.
>
> Simon


Michael Peppler [Team Sybase] Posted on 2010-07-12 04:52:09.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2010.07.12.04.52.03.696466@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4c35f08c$1@forums-1-dub> <4c3656df$1@forums-1-dub> <4c36f8aa$1@forums-1-dub> <4c37446b$1@forums-1-dub> <4c3747ef@forums-1-dub> <4c39e33b@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 11 Jul 2010 21:52:09 -0700
X-Trace: forums-1-dub 1278910329 10.22.241.152 (11 Jul 2010 21:52:09 -0700)
X-Original-Trace: 11 Jul 2010 21:52:09 -0700, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29354
Article PK: 78589


On Sun, 11 Jul 2010 08:28:59 -0700, Cory Sane [TeamSybase] wrote:

> I was always looking for events 45 & 92(cmdtxt with "create proc dyn)" & 46.
> If in every case you do the following then something is wrong:
> login(45),
> create proc(92+"create proc dyn"),
> execute it(38 sometimes hard to see)
> logout(46)
>
> My development team really had to play with their code to realize what they were doing.
> They never thought they were spawning new connections for every command.

Note that it's unlikely that java code (ie jConnect) would behave in the
same way. The multiple connections is a side effect of DBD::Sybase's
attempt to support multiple active statements on the same database handle
to "comply" with the DBI standard. I think that this was probably a
mistake (in retrospect), and the feature can be turned off with the
syb_no_child_con connection attribute.

Michael


Bret Halford Posted on 2010-07-08 16:48:23.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub>
In-Reply-To: <4c35f08c$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c360157$1@forums-1-dub>
Date: 8 Jul 2010 09:48:23 -0700
X-Trace: forums-1-dub 1278607703 10.22.241.152 (8 Jul 2010 09:48:23 -0700)
X-Original-Trace: 8 Jul 2010 09:48:23 -0700, vip152.sybase.com
Lines: 127
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29345
Article PK: 78579

You keep telling it to create new ones.

Rather than having your program repeatedly calling

> PreparedStatement stmt = con.prepareStatement("select XACT_NUM,

> XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");

call it just once to create the prepared statement. Once it is, well,
prepared, each time you want to execute that query reuse the prepared
statement, just change the parameter values using

> stmt.setInt(1,<new value>);

and then execute it.
> ResultSet rs = stmt.executeQuery();

and handle the results.





On 7/8/2010 9:36 AM, Simon wrote:
> ASE Environment:
> -->Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS
> 5.8/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009
> -->Solaris10
>
> Hi,
>
> I'm discovering preparedStatement.As far as I understand, one
> interesting point about preparedStatement is it's a precompiled and ASE
> will compile a 'lightweigh proc' for it and reuse it everytime the SQL
> is called.
>
> I'm now executing the same java sample code for several time and just
> notice that ASE keeps creating a proc for it. Isn't it supposed to use a
> previous LWP?
>
> Here's a sample of my java code (based on a sample code I got from
> jConnect for JDBC 7.0 doco)
>
> Properties props = new Properties();
> props.put("user", "SIMONM");
> props.put("password", "xxx");
> props.put("applicationname","simon");
> props.put("DYNAMIC_PREPARE","true");
> props.put("LITERAL_PARAMS","false");
>
> DriverManager.registerDriver(
> (Driver) Class.forName(
> "com.sybase.jdbc3.jdbc.SybDriver").newInstance());
> Connection con = DriverManager.getConnection(
> "jdbc:sybase:Tds:host:ip", props);
> /*
> * Create a statement object, the container for the SQL
> * statement. May throw a SQLException.
> */
> //Statement stmt = con.createStatement();
> PreparedStatement stmt = con.prepareStatement("select XACT_NUM,
> XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
> stmt.setInt(1,18819241);
> /*
> * Create a result set object by executing the query.
> * May throw a SQLException.
> */
> ResultSet rs = stmt.executeQuery();
>
>
> Now I execute it several time under Eclipse. Then I put some breakpoints
> in order to trace the SPID created at the next execution.
>
> On the UNIX server I execute the following commands:
> set tracefile "/tmp/simon.log" for xxx
> go
> set show_sqltext on
> go
>
> After the end of the tracing, here's the content of my log file
>
> ================================================================================
>
> Application Tracing report for spid -1 from application "simon"
> run by login "SIMONM" on host "EMEA-STC-LTD040"
> Tracing started on 2010/07/08 17:25:29.54
> ================================================================================
>
>
> 2010/07/08 17:25:41.43
> SQL Text: create proc dyn100 as select XACT_NUM, XACT_DAT, INST_NUM,
> PF_COD from xxx..ID_XACT where XACT_NUM = ?
>
> Sproc: sp_sql_type_name, Line: 0
>
> Sproc: sp_sql_type_name, Line: 18
>
> Sproc: sp_sql_type_name, Line: 20
>
> Sproc: sp_sql_type_name, Line: 23
>
> Sproc: sp_sql_type_name, Line: 33
>
> Sproc: sp_sql_type_name, Line: 35
>
> Sproc: sp_sql_type_name, Line: 57
>
> Sproc: sp_sql_type_name, Line: 65
>
> Sproc: sp_sql_type_name, Line: 75
>
> Sproc: sp_sql_type_name, Line: 0
>
> Sproc: sp_sql_type_name, Line: 0
>
> 2010/07/08 17:25:43.47
> No useful sqltext available.
>
> Prepared Statement: dyn100, SQL Text: select XACT_NUM, XACT_DAT,
> INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?End of Batch 5
>
> 2010/07/08 17:25:44.88
> End of Batch 6
>
> -->So my question is: Why did ASE recreate a LWP?
>
> Thanks to all for your hints


Simon Posted on 2010-07-09 08:12:18.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub>
In-Reply-To: <4c360157$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c36d9e2$1@forums-1-dub>
Date: 9 Jul 2010 01:12:18 -0700
X-Trace: forums-1-dub 1278663138 10.22.241.152 (9 Jul 2010 01:12:18 -0700)
X-Original-Trace: 9 Jul 2010 01:12:18 -0700, vip152.sybase.com
Lines: 146
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29347
Article PK: 78580

Le 08/07/2010 18:48, Bret Halford a écrit :

> You keep telling it to create new ones.
>
> Rather than having your program repeatedly calling
>
> > PreparedStatement stmt = con.prepareStatement("select XACT_NUM,
> > XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
>
> call it just once to create the prepared statement. Once it is, well,
> prepared, each time you want to execute that query reuse the prepared
> statement, just change the parameter values using
>
> > stmt.setInt(1,<new value>);
>
> and then execute it.
> > ResultSet rs = stmt.executeQuery();
>
> and handle the results.
>
>
>
>
>
> On 7/8/2010 9:36 AM, Simon wrote:
>> ASE Environment:
>> -->Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS
>> 5.8/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009
>> -->Solaris10
>>
>> Hi,
>>
>> I'm discovering preparedStatement.As far as I understand, one
>> interesting point about preparedStatement is it's a precompiled and ASE
>> will compile a 'lightweigh proc' for it and reuse it everytime the SQL
>> is called.
>>
>> I'm now executing the same java sample code for several time and just
>> notice that ASE keeps creating a proc for it. Isn't it supposed to use a
>> previous LWP?
>>
>> Here's a sample of my java code (based on a sample code I got from
>> jConnect for JDBC 7.0 doco)
>>
>> Properties props = new Properties();
>> props.put("user", "SIMONM");
>> props.put("password", "xxx");
>> props.put("applicationname","simon");
>> props.put("DYNAMIC_PREPARE","true");
>> props.put("LITERAL_PARAMS","false");
>>
>> DriverManager.registerDriver(
>> (Driver) Class.forName(
>> "com.sybase.jdbc3.jdbc.SybDriver").newInstance());
>> Connection con = DriverManager.getConnection(
>> "jdbc:sybase:Tds:host:ip", props);
>> /*
>> * Create a statement object, the container for the SQL
>> * statement. May throw a SQLException.
>> */
>> //Statement stmt = con.createStatement();
>> PreparedStatement stmt = con.prepareStatement("select XACT_NUM,
>> XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
>> stmt.setInt(1,18819241);
>> /*
>> * Create a result set object by executing the query.
>> * May throw a SQLException.
>> */
>> ResultSet rs = stmt.executeQuery();
>>
>>
>> Now I execute it several time under Eclipse. Then I put some breakpoints
>> in order to trace the SPID created at the next execution.
>>
>> On the UNIX server I execute the following commands:
>> set tracefile "/tmp/simon.log" for xxx
>> go
>> set show_sqltext on
>> go
>>
>> After the end of the tracing, here's the content of my log file
>>
>> ================================================================================
>>
>>
>> Application Tracing report for spid -1 from application "simon"
>> run by login "SIMONM" on host "EMEA-STC-LTD040"
>> Tracing started on 2010/07/08 17:25:29.54
>> ================================================================================
>>
>>
>>
>> 2010/07/08 17:25:41.43
>> SQL Text: create proc dyn100 as select XACT_NUM, XACT_DAT, INST_NUM,
>> PF_COD from xxx..ID_XACT where XACT_NUM = ?
>>
>> Sproc: sp_sql_type_name, Line: 0
>>
>> Sproc: sp_sql_type_name, Line: 18
>>
>> Sproc: sp_sql_type_name, Line: 20
>>
>> Sproc: sp_sql_type_name, Line: 23
>>
>> Sproc: sp_sql_type_name, Line: 33
>>
>> Sproc: sp_sql_type_name, Line: 35
>>
>> Sproc: sp_sql_type_name, Line: 57
>>
>> Sproc: sp_sql_type_name, Line: 65
>>
>> Sproc: sp_sql_type_name, Line: 75
>>
>> Sproc: sp_sql_type_name, Line: 0
>>
>> Sproc: sp_sql_type_name, Line: 0
>>
>> 2010/07/08 17:25:43.47
>> No useful sqltext available.
>>
>> Prepared Statement: dyn100, SQL Text: select XACT_NUM, XACT_DAT,
>> INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?End of Batch 5
>>
>> 2010/07/08 17:25:44.88
>> End of Batch 6
>>
>> -->So my question is: Why did ASE recreate a LWP?
>>
>> Thanks to all for your hints
>

Hi Bret,

I agree with you. Maybe I misunderstood how JAVA preparedStatement are
handled in ASE. AS far as I understand, the LWP created is kept in the
Procedure Cache even when the session ended. Correct me if I'm wrong.

Now I have a new question, our application is deployed on Weblogic. We
defined a pool of 'non' shrinkable connections therefore the session
don't end.
What happened if, in one session we call a query through a
preparedStatement then in another session we call the same query through
a preparedStatement. Will the other session use the LWP created by the
first session or create a new one?

Thanks


Cory Sane [TeamSybase] Posted on 2010-07-11 15:31:08.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub> <4c36d9e2$1@forums-1-dub>
In-Reply-To: <4c36d9e2$1@forums-1-dub>
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Lines: 150
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18197
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c39e3bc$1@forums-1-dub>
Date: 11 Jul 2010 08:31:08 -0700
X-Trace: forums-1-dub 1278862268 10.22.241.152 (11 Jul 2010 08:31:08 -0700)
X-Original-Trace: 11 Jul 2010 08:31:08 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29353
Article PK: 78584

a prepaed statement is session/connection based... they are managed in tempdb only findable by its own session.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Simon" <sst.misc@gmail.com> wrote in message news:4c36d9e2$1@forums-1-dub...
> Le 08/07/2010 18:48, Bret Halford a écrit :
>> You keep telling it to create new ones.
>>
>> Rather than having your program repeatedly calling
>>
>> > PreparedStatement stmt = con.prepareStatement("select XACT_NUM,
>> > XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
>>
>> call it just once to create the prepared statement. Once it is, well,
>> prepared, each time you want to execute that query reuse the prepared
>> statement, just change the parameter values using
>>
>> > stmt.setInt(1,<new value>);
>>
>> and then execute it.
>> > ResultSet rs = stmt.executeQuery();
>>
>> and handle the results.
>>
>>
>>
>>
>>
>> On 7/8/2010 9:36 AM, Simon wrote:
>>> ASE Environment:
>>> -->Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS
>>> 5.8/ase1503/2707/64-bit/FBO/Sun Jul 26 10:29:50 2009
>>> -->Solaris10
>>>
>>> Hi,
>>>
>>> I'm discovering preparedStatement.As far as I understand, one
>>> interesting point about preparedStatement is it's a precompiled and ASE
>>> will compile a 'lightweigh proc' for it and reuse it everytime the SQL
>>> is called.
>>>
>>> I'm now executing the same java sample code for several time and just
>>> notice that ASE keeps creating a proc for it. Isn't it supposed to use a
>>> previous LWP?
>>>
>>> Here's a sample of my java code (based on a sample code I got from
>>> jConnect for JDBC 7.0 doco)
>>>
>>> Properties props = new Properties();
>>> props.put("user", "SIMONM");
>>> props.put("password", "xxx");
>>> props.put("applicationname","simon");
>>> props.put("DYNAMIC_PREPARE","true");
>>> props.put("LITERAL_PARAMS","false");
>>>
>>> DriverManager.registerDriver(
>>> (Driver) Class.forName(
>>> "com.sybase.jdbc3.jdbc.SybDriver").newInstance());
>>> Connection con = DriverManager.getConnection(
>>> "jdbc:sybase:Tds:host:ip", props);
>>> /*
>>> * Create a statement object, the container for the SQL
>>> * statement. May throw a SQLException.
>>> */
>>> //Statement stmt = con.createStatement();
>>> PreparedStatement stmt = con.prepareStatement("select XACT_NUM,
>>> XACT_DAT, INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?");
>>> stmt.setInt(1,18819241);
>>> /*
>>> * Create a result set object by executing the query.
>>> * May throw a SQLException.
>>> */
>>> ResultSet rs = stmt.executeQuery();
>>>
>>>
>>> Now I execute it several time under Eclipse. Then I put some breakpoints
>>> in order to trace the SPID created at the next execution.
>>>
>>> On the UNIX server I execute the following commands:
>>> set tracefile "/tmp/simon.log" for xxx
>>> go
>>> set show_sqltext on
>>> go
>>>
>>> After the end of the tracing, here's the content of my log file
>>>
>>> ================================================================================
>>>
>>>
>>> Application Tracing report for spid -1 from application "simon"
>>> run by login "SIMONM" on host "EMEA-STC-LTD040"
>>> Tracing started on 2010/07/08 17:25:29.54
>>> ================================================================================
>>>
>>>
>>>
>>> 2010/07/08 17:25:41.43
>>> SQL Text: create proc dyn100 as select XACT_NUM, XACT_DAT, INST_NUM,
>>> PF_COD from xxx..ID_XACT where XACT_NUM = ?
>>>
>>> Sproc: sp_sql_type_name, Line: 0
>>>
>>> Sproc: sp_sql_type_name, Line: 18
>>>
>>> Sproc: sp_sql_type_name, Line: 20
>>>
>>> Sproc: sp_sql_type_name, Line: 23
>>>
>>> Sproc: sp_sql_type_name, Line: 33
>>>
>>> Sproc: sp_sql_type_name, Line: 35
>>>
>>> Sproc: sp_sql_type_name, Line: 57
>>>
>>> Sproc: sp_sql_type_name, Line: 65
>>>
>>> Sproc: sp_sql_type_name, Line: 75
>>>
>>> Sproc: sp_sql_type_name, Line: 0
>>>
>>> Sproc: sp_sql_type_name, Line: 0
>>>
>>> 2010/07/08 17:25:43.47
>>> No useful sqltext available.
>>>
>>> Prepared Statement: dyn100, SQL Text: select XACT_NUM, XACT_DAT,
>>> INST_NUM, PF_COD from xxx..ID_XACT where XACT_NUM = ?End of Batch 5
>>>
>>> 2010/07/08 17:25:44.88
>>> End of Batch 6
>>>
>>> -->So my question is: Why did ASE recreate a LWP?
>>>
>>> Thanks to all for your hints
>>
>
> Hi Bret,
>
> I agree with you. Maybe I misunderstood how JAVA preparedStatement are handled in ASE. AS far as I understand, the LWP created
> is kept in the Procedure Cache even when the session ended. Correct me if I'm wrong.
>
> Now I have a new question, our application is deployed on Weblogic. We defined a pool of 'non' shrinkable connections
> therefore the session don't end.
> What happened if, in one session we call a query through a preparedStatement then in another session we call the same query
> through a preparedStatement. Will the other session use the LWP created by the first session or create a new one?
>
> Thanks


Simon Posted on 2010-07-12 08:31:26.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub> <4c36d9e2$1@forums-1-dub> <4c39e3bc$1@forums-1-dub>
In-Reply-To: <4c39e3bc$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3ad2de$1@forums-1-dub>
Date: 12 Jul 2010 01:31:26 -0700
X-Trace: forums-1-dub 1278923486 10.22.241.152 (12 Jul 2010 01:31:26 -0700)
X-Original-Trace: 12 Jul 2010 01:31:26 -0700, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29356
Article PK: 78586

Le 11/07/2010 17:31, Cory Sane [TeamSybase] a écrit :

> a prepaed statement is session/connection based... they are managed in
> tempdb only findable by its own session.
>

Hi Cory,

So if I understand well,
if user 1 is submitting a query (a preparedStatement is called
behind),let's imagine Weblogic uses connection 2 from 10 available
connections in the JDBC pool (each connection from the pool is a durable
connection to the DB). The prepared statement is compiled and executed.

Then

If user 2 is submitting almost the same query (only the parameter
changes), and let's imagine again that Weblogic m by chancem redirects
this request to connection 2 and as the session/connection are never
closed it can reuse the LWP.

But as we cannot control this behaviour we can say that every
preparedStatment will create a new lwp?

Simon


J Posted on 2010-07-12 16:17:43.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Reply-To: J@bogusemailAddress.com
Message-ID: <4c3b3fd5.869796234@forums.sybase.com>
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub> <4c36d9e2$1@forums-1-dub> <4c39e3bc$1@forums-1-dub> <4c3ad2de$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 12 Jul 2010 09:17:43 -0700
X-Trace: forums-1-dub 1278951463 10.22.241.152 (12 Jul 2010 09:17:43 -0700)
X-Original-Trace: 12 Jul 2010 09:17:43 -0700, vip152.sybase.com
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29357
Article PK: 78588

On 12 Jul 2010 01:31:26 -0700, Simon <sst.misc@gmail.com> wrote:

Is there any chance you could consider not using PreparedStatements
but instead use statement cache and literal autoparms, then just let
ASE re-use the plans across all connections?

Jay

>Le 11/07/2010 17:31, Cory Sane [TeamSybase] a écrit :
>> a prepaed statement is session/connection based... they are managed in
>> tempdb only findable by its own session.
>>
>Hi Cory,
>
>So if I understand well,
>if user 1 is submitting a query (a preparedStatement is called
>behind),let's imagine Weblogic uses connection 2 from 10 available
>connections in the JDBC pool (each connection from the pool is a durable
>connection to the DB). The prepared statement is compiled and executed.
>
>Then
>
>If user 2 is submitting almost the same query (only the parameter
>changes), and let's imagine again that Weblogic m by chancem redirects
>this request to connection 2 and as the session/connection are never
>closed it can reuse the LWP.
>
>But as we cannot control this behaviour we can say that every
>preparedStatment will create a new lwp?
>
>Simon


Cory Sane[TeamSybase] Posted on 2010-07-12 18:29:03.0Z
From: "Cory Sane[TeamSybase]" <xxxx>
Newsgroups: sybase.public.ase.general
Organization: 169.200.173.52
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub> <4c36d9e2$1@forums-1-dub> <4c39e3bc$1@forums-1-dub> <4c3ad2de$1@forums-1-dub> <4c3b3fd5.869796234@forums.sybase.com>
X-Newsreader: AspNNTP 1.50 (JodoHost)
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3b5eef@forums-1-dub>
Date: 12 Jul 2010 11:29:03 -0700
X-Trace: forums-1-dub 1278959343 10.22.241.152 (12 Jul 2010 11:29:03 -0700)
X-Original-Trace: 12 Jul 2010 11:29:03 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29358
Article PK: 78590

Jay,

I think your idea may be best for Simon... Prepared statements were created
before the idea of statement cache.

Cory


On 12 Jul 2010 09:17:43 -0700,
in sybase.public.ase.general

J <jtotally_bogus@sbcglobal.net> wrote:
>On 12 Jul 2010 01:31:26 -0700, Simon <sst.misc@gmail.com> wrote:
>
>Is there any chance you could consider not using PreparedStatements
>but instead use statement cache and literal autoparms, then just let
>ASE re-use the plans across all connections?
>
>Jay
>
>>Le 11/07/2010 17:31, Cory Sane [TeamSybase] a écrit :
>>> a prepaed statement is session/connection based... they are managed in
>>> tempdb only findable by its own session.
>>>
>>Hi Cory,
>>
>>So if I understand well,
>>if user 1 is submitting a query (a preparedStatement is called
>>behind),let's imagine Weblogic uses connection 2 from 10 available
>>connections in the JDBC pool (each connection from the pool is a durable
>>connection to the DB). The prepared statement is compiled and executed.
>>
>>Then
>>
>>If user 2 is submitting almost the same query (only the parameter
>>changes), and let's imagine again that Weblogic m by chancem redirects
>>this request to connection 2 and as the session/connection are never
>>closed it can reuse the LWP.
>>
>>But as we cannot control this behaviour we can say that every
>>preparedStatment will create a new lwp?
>>
>>Simon
>


Simon Posted on 2010-07-13 07:21:33.0Z
From: Simon <sst.misc@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE 15.0.3 preparedStatement/ProcedureCache question
References: <4c35f08c$1@forums-1-dub> <4c360157$1@forums-1-dub> <4c36d9e2$1@forums-1-dub> <4c39e3bc$1@forums-1-dub> <4c3ad2de$1@forums-1-dub> <4c3b3fd5.869796234@forums.sybase.com> <4c3b5eef@forums-1-dub>
In-Reply-To: <4c3b5eef@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3c13fd$1@forums-1-dub>
Date: 13 Jul 2010 00:21:33 -0700
X-Trace: forums-1-dub 1279005693 10.22.241.152 (13 Jul 2010 00:21:33 -0700)
X-Original-Trace: 13 Jul 2010 00:21:33 -0700, vip152.sybase.com
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29359
Article PK: 78591

Yes you're right, I thin I need to make up a POC for our R&D to use
StatementCache and Literal autoparams.

Thanks for your help.

Le 12/07/2010 20:29, Cory Sane[TeamSybase] a écrit :

> Jay,
>
> I think your idea may be best for Simon... Prepared statements were created
> before the idea of statement cache.
>
> Cory
>
>
> On 12 Jul 2010 09:17:43 -0700,
> in sybase.public.ase.general
> J<jtotally_bogus@sbcglobal.net> wrote:
>> On 12 Jul 2010 01:31:26 -0700, Simon<sst.misc@gmail.com> wrote:
>>
>> Is there any chance you could consider not using PreparedStatements
>> but instead use statement cache and literal autoparms, then just let
>> ASE re-use the plans across all connections?
>>
>> Jay
>>
>>> Le 11/07/2010 17:31, Cory Sane [TeamSybase] a écrit :
>>>> a prepaed statement is session/connection based... they are managed in
>>>> tempdb only findable by its own session.
>>>>
>>> Hi Cory,
>>>
>>> So if I understand well,
>>> if user 1 is submitting a query (a preparedStatement is called
>>> behind),let's imagine Weblogic uses connection 2 from 10 available
>>> connections in the JDBC pool (each connection from the pool is a durable
>>> connection to the DB). The prepared statement is compiled and executed.
>>>
>>> Then
>>>
>>> If user 2 is submitting almost the same query (only the parameter
>>> changes), and let's imagine again that Weblogic m by chancem redirects
>>> this request to connection 2 and as the session/connection are never
>>> closed it can reuse the LWP.
>>>
>>> But as we cannot control this behaviour we can say that every
>>> preparedStatment will create a new lwp?
>>>
>>> Simon
>>