DBQlogTbl statementtype is null?

Database
Enthusiast

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.


7 REPLIES
Supporter

Re: DBQlogTbl statementtype is null?

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

Enthusiast

Re: DBQlogTbl statementtype is null?

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.

Supporter

Re: DBQlogTbl statementtype is null?

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?

Enthusiast

Re: DBQlogTbl statementtype is null?

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

Enthusiast

Re: DBQlogTbl statementtype is null?






































































  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 ?                                                                  
Supporter

Re: DBQlogTbl statementtype is null?

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...

Enthusiast

Re: DBQlogTbl statementtype is null?

Thanks for your help.

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