All Forums Database
ditsy74 9 posts Joined 08/12
02 Oct 2012
DBQlogTbl statementtype is null?

I am doing some sql logging, but I don't understand why it's working this way.
There is this macro called by javascript code ONLY ONE TIME that is appearing multiple times in the log (4 or more). 
I'm trying to figure out why it is showing multiple times in the log.
I also noticed that the statementtype column value for this macro is NULL, instead of SELECT.
What could be the reason? 
Thanks.
 
 

 

ulrich 816 posts Joined 09/09
03 Oct 2012

Can you share your Java connection settings - without username and password? Do you connect with multiple sessions?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ditsy74 9 posts Joined 08/12
03 Oct 2012

 

I use an ODBC System DSN.

 

These are settings, if it helps:

 

- DS Name /Desc...

 

- UserName/Password ...

 

- Do not resolve alias name to IP Address - checked

 

- Use Integrated Security (NOT Checked)

 

- Default DB ...

 

- Session Char Set : UTF8

 

- Use Column Names - checked

 

- Use Regional Settings for Decimal Symbol - checked

 

- Return Output Params as Resultset - checked

 

- Enable Extended Statement Info - checked

 

- Session Mode: TeraData

 

- DateTIme Format: III

 

- Return Generated Keys: No

 

- Max Response Size Buffer: 8192

 

- Redisplay Reconnect Wait: 10

 

- TDMST Port # - 1025

 

- State Check Level: 0

 

- Login TImeout: 20

 

- ProcedureWithPrintStmt: N

 

- ProcedureWithSPLSource: Y

 

- Use TCP_NODELAY - checked

 

- Retry System calls (EINTR) - checked

 

 

 

I am curious as to why it is showing multiple times in the log. Does it mean it is executing multiple times? The other macros called only show once in the log, but this one is different. I did a test, and called another macro right next to this one, using the same connection, and it only showed once.

 

Thanks.

 

 

ulrich 816 posts Joined 09/09
03 Oct 2012

you called ODBC via javascript?
In the cases I saw this happen the query was only executed once - you can validate this on your own by checking the totalIO and AmpCPUTime values. They should be >0 only for one of the rows.
Do you prepare the statements before you execute them?
Is the parsingCPUtime >0 for all statememts?
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ditsy74 9 posts Joined 08/12
03 Oct 2012

 

TotalIO and ParserCPUTime are all > 0.

 

ParserCPUTime has 2 rows > 0 and 6 rows = 0.

 

 

 

No. We are using Javascript server code in this example, and no prepare. This is a sample call:

 

 

 

 

Set conConnection = Server.CreateObject("ADODB.Connection")

conConnection.ConnectionTimeout = 900

conConnection.CommandTimeout=9999

 

str_ProductDetails = str_ProductDetails & " EXECUTE m_GetProducts_For_Maint_Screen('" & str_QuoteNumber & "', '" & str_RenewalTerm & "');"

 

Set rs_ProductDetails = Server.CreateObject("ADODB.RecordSet")

 

With rs_ProductDetails

.CursorLocation = 2 

.ActiveConnection = conConnection

.Source = str_ProductDetails

.Open

End with

ditsy74 9 posts Joined 08/12
03 Oct 2012

 

 

CollectTimeStamp

RequestNum

InternalRequestNum

LogonDateTime

AcctStringTime

AcctStringHour

AcctStringDate

AppID

QueryBand

ProfileID

StartTime

FirstStepTime

FirstRespTime

LastStateChange

NumSteps

NumStepswPar

MaxStepsInPar

NumResultRows

TotalIOCount

AMPCPUTime

ParserCPUTime

UtilityByteCount

UtilityRowCount

ErrorCode

ErrorText

WarningOnly

DelayTime

WDDelayTime

AbortFlag

CacheFlag

StatementType

QueryText

NumOfActiveAMPs

MaxAMPCPUTime

MaxCPUAmpNumber

MinAmpCPUTime

MaxAmpIO

MaxIOAmpNumber

MinAmpIO

SpoolUsage

WDID

OpEnvID

SysConID

LSN

NoClassification

WDOverride

SLGMet

ExceptionValue

FinalWDID

TDWMEstMaxRows

TDWMEstLastRows

TDWMEstTotalTime

TDWMAllAmpFlag

TDWMConfLevelUsed

TDWMRuleID

AMPCPUTimeNorm

ParserCPUTimeNorm

MaxAMPCPUTimeNorm

MaxCPUAmpNumberNorm

MinAmpCPUTimeNorm

EstResultRows

EstProcTime

EstMaxRowCount

ProxyUser

ProxyRole

StatementGroup

SessionTemporalQualifier

ExtraField1

ExtraField2

ExtraField3

ExtraField4

ExtraField5

ExtraField6

ExtraField7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1

10/3/2012 11:49:31.65

53

862

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:32.55

10/3/2012 11:49:32.56

10/3/2012 11:49:33.23

?

19

3

3

8.00

1,977.00

0.36

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

7

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

23.94

0.00

4.30

7

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2

10/3/2012 11:49:31.65

52

861

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:31.67

10/3/2012 11:49:31.67

10/3/2012 11:49:32.53

?

19

3

3

8.00

1,977.00

0.34

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.07

1

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

23.13

0.00

4.57

1

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3

10/3/2012 11:49:31.65

51

860

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:30.96

10/3/2012 11:49:30.96

10/3/2012 11:49:31.65

?

19

3

3

8.00

1,977.00

0.38

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

5

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

25.29

0.00

4.04

5

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4

10/3/2012 11:49:07.76

48

857

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:28.41

10/3/2012 11:49:28.41

10/3/2012 11:49:29.27

?

19

3

3

8.00

1,977.00

0.36

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

0

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

23.94

0.00

4.30

0

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5

10/3/2012 11:49:07.76

50

859

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:30.16

10/3/2012 11:49:30.16

10/3/2012 11:49:30.94

?

19

3

3

8.00

1,978.00

0.36

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

3

0.00

276.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

24.21

0.00

3.77

3

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6

10/3/2012 11:49:07.76

49

858

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:29.28

10/3/2012 11:49:29.28

10/3/2012 11:49:30.14

?

19

3

3

8.00

1,977.00

0.36

0.00

?

?

0

?

 

?

?

 

T

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

2

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

24.48

0.00

4.04

2

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7

10/3/2012 11:49:07.76

47

856

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:24.81

10/3/2012 11:49:27.51

10/3/2012 11:49:28.40

?

19

3

3

8.00

1,977.00

0.35

2.08

?

?

0

?

 

?

?

 

 

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

1

0.00

275.00

3

189.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

23.67

139.88

4.04

1

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

118

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

8

10/3/2012 11:49:07.76

44

853

10/3/2012 11:48:35.22

?

?

?

W3WP                          

?

?

10/3/2012 11:49:19.46

10/3/2012 11:49:23.14

10/3/2012 11:49:23.90

?

19

3

3

8.00

1,978.00

0.35

2.07

?

?

0

?

 

?

?

 

 

Null                

EXEC m_GetProducts_For_Maint_Screen ( 'TD31137' , 'Y' ) ;

8

0.06

5

0.00

275.00

3

190.00

9,711,616.00

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

23.67

139.34

4.04

5

0.00

36,470,082.00

4,801.67

48,626,776.00

?

?

Select                                                      

?

?

?

117

?

?

1.00

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ulrich 816 posts Joined 09/09
03 Oct 2012

Hm, the DBQL is indicating that the query was executed 8 times - 6 queries plans had been used from cache -> parsingCPUTime = 0.
So maybe your code is not doing what you expect...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ditsy74 9 posts Joined 08/12
03 Oct 2012

Thanks for your help.
We found out it was a code problem, a recordset filter that, when set,  was resubmitting the macro multiple times.
 
 
 

You must sign in to leave a comment.