Jump to content


Photo
- - - - -

Slow SQL execution


25 replies to this topic

#1 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 29 December 2004 - 06:34 AM

Hi, I have a Superlayer function which experience a very slow performance. (PROIV 4.6 and Oracle 9i)

It takes 5 minutes to finish which user considers unbearable.

I have enabled the trance file and found that the problem statement is:

============================================
112307.673-setls: 'WOCOMITU' ls 2 entry
112307.673-qgSqlFProc - FCB = 011AAF04, PFCB->FCRB = 00008004
112307.673-qgSqlFProc - NO TIB EXISTS
112307.673-qgSqlFProc: Open File and allocate new TIB
112307.673-qgStmCmp: XCPT_THROW [STANDARD] (fMode||FInst !=)
112307.673- fMode1 0 fMode2 0 pFInst1 011F8E0C pFInst2 011F8E5C
112307.673-qgSqlFOpen: tib 008215F0, pFcb 011AAF04, 'WO'
112307.673- Current ProIV function WOCOMITU ls 2 fldno 2, curfile 10, 'WO'
112307.673-qgSqlFProc: FCB TIB = 008215F0
112307.673-qgPrmPreFSql: FULL FUNCTION COMMAND
112307.673-qgPrmPreRefs: ColumnReference ptr = 011F8E6C
112307.673-qgSqlFProc: READ COMMAND **DOES NOT** EXIST
112307.673-TelnetCheckBreak: returned FALSE
112307.673-check_int: 0 pending chars
112307.673-qgSqlFOpen: tib 008215F0, pFcb 011AAF04, 'WO'
112307.673- Current ProIV function WOCOMITU ls 2 fldno 2, curfile 10, 'WO'
112307.673-sysfopn: ftyp 10, isfd 99, errno/fnl.filerrno 0/10012 'WO'
112307.673-conn0sub: opnfls[2] = 011AAF04 added, isfd 0, fnlevel 0, currFnlevel 0, rfname WO
112307.673-sysfrd: isfd 0, SY_GE, rlokf: M F S ~ ~ ~ S I N ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 4 3 9 7 4 0 0 0
112307.673- 1
112307.673-qgSqlFRead: pTib 008215F0, pFcb 011AAF04, fAccess 412 (FIRST|FROMCUR|LOCK)
112307.673- pFcb = 011AAF04, pTib->pRdCmd = 008212B4, pTib->pSndRdCmd = 00000000
112307.673- Current ProIV function WOCOMITU ls 2 fldno 2, curfile 10, 'WO'
112307.673-qgPrmPreFSql: FULL FUNCTION COMMAND
112307.673-qgSqlFRead: (bIsFullSql && bUsePriCmd) calling qgPrmPreRefs, pTib->Ind[0] = 0
112307.673-qgPrmPreRefs: ColumnReference ptr = 011F8E6C
112307.673-qgStmCmp: exit TRUE, fMode1 0 fMode2 0 pFInst1 011F8E5C pFInst2 011F8E5C
112307.673-qgPrmCmp: Compare header - THE SAME
112307.673-qgStmCmp: exit TRUE, fMode1 524289 fMode2 524289 pFInst1 00000000 pFInst2 00000000
112307.673-qgPrmCbPrepare: Prepare Parameter Control Block
112307.673-qgPrmCbPrepare - CMD TYPE = SELECT
112307.673-qgPrmCbPrepare - Prepare Output SQLDA
112307.673-OraSqldaVInit: Alloc heap(256), pSqlda->hHeap = 00821200
112307.673-OraSqldaVInit: Alloc PVars: pSqlda->pVars = 0120ED14, SIZE = 1040
112307.673-OraSqldaReset: pSqlda = 012548CC fParse = 5
112307.673-OraSqldaVInit: Alloc heap(256), pSqlda->hHeap = 008211C0
112307.673-OraSqldaVInit: Alloc PVars: pSqlda->pVars = 0120F14C, SIZE = 1040
112307.673-OraSqldaReset: pSqlda = 01254904 fParse = 3
112307.673-qgPrmCbBndCol: Binding Column 0
112307.673-qgPrmCbBndCol: Binding Column 1
112307.673-qgPrmCbBndCol: Binding Column 2
112307.673-qgPrmCbBndCol: Binding Column 3
112307.673-qgPrmCbBndCol: Binding Column 4
112307.673-qgPrmCbBndCol: Binding Column 5
112307.673-qgPrmCbBndCol: Binding Column 6
112307.673-qgPrmCbBndCol: Binding Column 7
112307.673-qgPrmCbBndCol: Binding Column 8
112307.673-qgPrmCbBndCol: Binding Column 9
112307.673-OraCmdParse: SQL STATEMENT(length = 411) =
112307.673- SELECT CCN,MAS_LOC,WO_NUM,WO_LINE,START_DATE,MFG_CLOSE_DATE,RTNG
112307.673- _USED,ATLEAST_ONE_COMMIT_DONE,STATUS,LAST_PICK_LIST FROM WO WHE
112307.673- RE CCN = :P02609 AND MAS_LOC = :P02614 AND (:P02621 = '
112307.673- ' OR ( WO_NUM BETWEEN :P04432 AND :P04466 ) ) AND (:P0263
112307.673- 3 = ' ' OR ( WO_LINE BETWEEN :P04449 AND :P04483 ) ) FOR U
112307.673- PDATE OF START_DATE,MFG_CLOSE_DATE,RTNG_USED,ATLEAST_ONE_COMMIT
112307.673- _DONE,STATUS,LAST_PICK_LIST
112307.673-OraCmdParse: Statement Prepare OK - rc = 0
112307.673-OraCmdOBind: OUTPUT BIND - Total Columns = 10
112307.673- Col 1 : BindType 1, Data 011A2814, Name CCN
112307.673- Col 2 : BindType 1, Data 011A281A, Name MAS_LOC
112307.673- Col 3 : BindType 1, Data 011A281D, Name WO_NUM
112307.673- Col 4 : BindType 1, Data 011A2831, Name WO_LINE
112307.673- Col 5 : BindType 1, Data 011A2859, Name START_DATE
112307.673-OraCmdOBind: Clearing I/O area Data 011A2859, Len 10
112307.673- Col 6 : BindType 1, Data 011A2881, Name MFG_CLOSE_DATE
112307.673-OraCmdOBind: Clearing I/O area Data 011A2881, Len 10
112307.673- Col 7 : BindType 1, Data 011A28D7, Name RTNG_USED
112307.673- Col 8 : BindType 1, Data 011A32E3, Name ATLEAST_ONE_COMMIT_DONE
112307.673- Col 9 : BindType 1, Data 011A33B8, Name STATUS
112307.673- Col 10: BindType 6, Data 011A35EA, Name LAST_PICK_LIST
112307.673-OraCmdIBind: INPUT BIND - Total Columns = 8
112307.673- Col 1 : BindType 9, Name :P02609 Data Addr 0082109C
112307.673- Data(Length = 8) = MFS

112307.673- Col 2 : BindType 9, Name :P02614 Data Addr 008210AC
112307.673- Data(Length = 5) = SIN

112307.673- Col 3 : BindType 9, Name :P02621 Data Addr 008210B8
112307.673- Data(Length = 22) = 43974

112307.673- Col 4 : BindType 9, Name :P04432 Data Addr 008210D4
112307.673- Data(Length = 32) = 43974

112307.673- Col 5 : BindType 9, Name :P04466 Data Addr 008210FC
112307.673- Data(Length = 32) = 43974

112307.673- Col 6 : BindType 9, Name :P02633 Data Addr 00821124
112307.673- Data(Length = 6) = 0001

112307.673- Col 7 : BindType 9, Name :P04449 Data Addr 00821130
112307.673- Data(Length = 32) = 0001

112307.673- Col 8 : BindType 9, Name :P04483 Data Addr 00821158
112307.673- Data(Length = 32) = 0001

112718.764-OraCmdExecFetch: Execute OK - rc = 0
112718.764-sysfrd: exit, stat 3875, errno/fnl.filerrno 0/10012
112718.764- M F S ~ ~ ~ S I N ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 4 3 9 7 4 0 0 0
112718.764- 1 061 1 1 7 4 8 03S Y S 1fC o m m i t ~ Q u a n t i t y ~ f o r
112718.764- ~ W o r k 2 0 0 4 1 2 2 7 ~ ~ 12E H 1 ~ 2 ~ C H S L 7 r 1 0 1 .
112718.764- 0 1 B 05p 8110p B 05t 871000000000000000000000A 0385p 7f7f7fg A
112718.764- 04# c A 041602a A 0413` a B 05G ) ~ b @ 01p B 03101010c 1e7 4 8
112718.764- 7 1 1 1 1 7 5 1 S Y S * ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ @ 02w ~ A
112718.764- 0310@ I W G I _ M A I N T u 06* P 4 S L * i 0bE n v i r o n m e
112718.764- n t D B A ) 0e~ ~ ~ ~ ~ ~ ~ ~ ~ ~ S Y S ) 0e( T Y 0 0 0 1 7 9 /
112718.764- S Y S ) 0e( T Y 0 0 0 1 8 3 / S Y S ) 0e( T Y 0 0 0 1 8 3 / S Y
112718.764- S ) 0e( T Y 0 0 0 1 8 4 / S Y S ) 0e( T Y 0 0 0 1 8 4 / S Y S )
112718.764- 0e( T Y 0 0 0 1 8 7 / S Y S ) 0e( T Y 0 0 0 2 2 1 / S Y S ) 0e(
112718.764- T Y 0 0 0 5 0 2 / S Y S ) 0e( T Y 0 0 0 5 0 2 / S Y S ) 0e( T Y
112718.764- 0 0 0 5 0 2 / S Y S ) 0e( T Y 0 0 0 1 8 2 / S Y S ) { @ 0213D 03
112718.764- 12X 997 80@ 0213E 0a1 B 5 B 3 3 3 2 7 E 0a1 B 5 B 3 3 3 3 7 E 0a
112718.764- 1 B 5 B 3 3 3 4 7 E f 061 B 2 8 3 0 061 B 2 8 4 2 0a1 B 5 B 3 1
112718.764- 4 2 0 8 027 8 027 1 026 C 027 7 026 B 027 4 h A 031310@ 010 ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ 1 ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ M F S ~ ~ ~ M F S
112718.764- ~ ~ ~ M F S ~ ~ ~ M F S ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
112718.764- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
================================================================

You can see that there is a gap bwteen 11:23 to 11:27 which explains the 5 minutes execution time.

The statement is just a simple SELECT for UPDATE.

Whatelse I can check for the problem.

For you reference, the original SL function is a U type function which read a Table in "C" mode. At the DEFAULT Logic it issues the SQL SELECT statement.

Any help will be appreciated.

Regards,

Vol

#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 29 December 2004 - 04:44 PM

Assuming you do not expect your SELECT to return a large number of records, one might guess the Oracle optimizer is not executing your statement in an efficient way.

Something to try is the equivalent of your SELECT in SQL*Plus with explain plan (or whatever applies in your favored toolset) and see what access strategy Oracle is actually using to retrieve the data.

Obvious other questions are whether you have a relevant index (I would think so!) and whether optimizer statistics are properly maintained (assuming cost-based optimizer).

Another important possibility that you should eliminate, since this is a SELECT FOR UPDATE, is that it is simply waiting for a record lock - knowing nothing about your application, I couldn't comment on that. If the problem is consistent though that is maybe less likely?

You don't tell us whether the problem appeared suddenly (with unchanged source code) or whether the specific function or some other part of the system (including the database) has recently been changed or restored etc. That has to be important.
Nothing's as simple as you think

#3 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 29 December 2004 - 04:57 PM

Vol,

There are some really useful SQL tools that you can use. I don't remember off hand the Oracle version, but PostgreSQL has an explain command. By inserting explain before the SQL command, postgreSQL tells you how efficiently it can retrieve the data.

Your SQL Statement is:

SELECT

CCN,MAS_LOC,WO_NUM,WO_LINE,START_DATE,MFG_CLOSE_DATE,RTNG_USED,ATLEAST_ONE_COMMI
_DONE,STATUS,LAST_PICK_LIST

FROM WO

WHERE
CCN = :P02609 AND
MAS_LOC = :P02614 AND (:P02621 = '112307.673- ' OR (WO_NUM BETWEEN :P04432 AND :P04466) )
AND (:P0263 = ' ' OR (WO_LINE BETWEEN :P04449 AND :P04483 ) )

FOR UPDATE OF START_DATE,MFG_CLOSE_DATE,RTNG_USED,ATLEAST_ONE_COMMIT_DONE,STATUS,LAST_PICK_LIS


It looks like the "worst" part of the SQL is the SELECT criteria comes following the MAS_LOC portion.

Some things to look at:

I'm assuming that this is coming from a sel-partial. However, what exactly is the SEL logic and what is the key structure of WO?

How many rows are in the table WO and how many are you actually selecting?

Can you embed "better" SQL?

hth,

Joseph

#4 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 30 December 2004 - 01:45 AM

Thanks for reply.

I used the same SQL script in SQLPLUS and it ended in a 10 seconds or so.

The SELECT statement is translated by PROIV. The actual statement in DEFAULT logic for a group where WO is the only file in C mode:

002 001 * !! Group: RD_WO - Default Logic
002 * restore the variables
003 WO_NUM = $WO_NUM
004 WO_LINE = $WO_LINE
005 * $RANGE = CCN(1,6) + MAS_LOC(1,3) + $END_WO_NUM(1,20) + $END_WO_LINE
006 * SEL-RANGE($RANGE)
007 SQL
008 SELECT
009 FROM WO
010 WHERE
011 CCN = :CCN AND
012 MAS_LOC = :MAS_LOC AND
013 (:WO_NUM = ' ' OR
014 (WO_NUM BETWEEN :$WO_NUM AND :$END_WO_NUM)) AND
015 (:WO_LINE = ' ' OR
016 (WO_LINE BETWEEN :$WO_LINE AND :$END_WO_LINE))
017 ENDSQL

I do not know if the slowness happen suddenly or not. I just receive complain by customer and about to take a look on this. In my testing enviornment (of course with much lesser data volume) I do not feel anything. But by using Customer's Data, the function takes 5 minutes to complete.

There are about 300K rows in WO table.

Also, acutally the starter screen passes ONE WO_NUM and ONE WO_LINE to this function.

Regards,

Vol

#5 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 30 December 2004 - 02:43 PM

I used the same SQL script in SQLPLUS and it ended in a 10 seconds or so.

You can't have used the exact same text in SQL plus. Precisely what SQL text took ten seconds?

I don't know if it's all or part of your problem but I notice that lines 13 and 15 in your ProIV SQL are testing the value of a host variable not a SQL column ?!

That looks a pretty bizarre thing to do given you already know the value of the host variable.
It seems like maybe just a lazy way to allow the order number and line to be optionally unrestricted using only one SQL statement?
That might be defeating the optimizer.

As always, you might well do better by expressing more clearly to the database what you are doing

For example, if I presume correctly that a '' WO_NUM will always imply a '' WO_LINE..
then you effectively need three different SQL statements viz:

IF $WO_NUM = '' THEN
   SQL
   SELECT FROM WO
   WHERE CCN = :CCN
       AND MAS_LOC = :MAS_LOC
   ENDSQL
ELSE IF $WO_LINE = '' THEN 
   SQL
   SELECT FROM WO
   WHERE CCN = :CCN
       AND MAS_LOC = :MAS_LOC
       AND WO_NUM BETWEEN :$WO_NUM AND :$END_WO_NUM
   ENDSQL
ELSE
   SQL
   SELECT FROM WO
   WHERE CCN = :CCN
       AND MAS_LOC = :MAS_LOC
       AND WO_NUM BETWEEN :$WO_NUM AND :$END_WO_NUM
       AND WO_LINE BETWEEN :$WO_LINE AND :$END_WO_LINE
   ENDSQL
;;;
HTH?

PS. Remember also there is no proper distinction in Oracle between '' and NULL, I don't know if that might have been muddying the waters also.

PPS. It's bad practice to use file variables (ie. CCN, MAS_LOC) as host variables, scratch variables are always prefererable (although it's unlikely to be causing any problem in your example I think).
Nothing's as simple as you think

#6 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 01 January 2005 - 03:55 AM

Happy New Year Richard,

I rewrite the SQL as you have suggested and it seems fix the problem!!!!! Great!!! Thanks. :)

The only thing I have changed is that I put two ; instead of 3, :)

Regards,

Vol

#7 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 01 January 2005 - 09:59 AM

Hi Richard,

You say "it bad practice to use file variables"... why is that?

I've never had any problems with that so far....

Ta,

Rob.

#8 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 02 January 2005 - 07:36 PM

Rob,

You say "it bad practice to use file variables"... why is that?
I've never had any problems with that so far....


OK, "bad practice" was a bit strong maybe but I personally feel it is. It's just that when things goes wrong, which they do sometimes, expecially for newbie ProIV programmers, it's very confusing and wastes time and money. Hence I always suggest people have a scratch-vars-only rule.

I also happen to think that sticking to scratch variables makes the distinction between host variables and columns even more obvious. People do seem to get confused sometimes, surprising as it may seem. (For example, although it was of course correct, I misread/misunderstood the test :WO_NUM = '' in Vol's example the first time around).

In the ProIV docs it just says:

"Exercise care when using file variables as host variables in Full Function SQL statements. The values of any file variables used by the SELECT are taken during the read cycle for the file, and not at the time of the logic execution. It is therefore recommended that file variables are used only if their values will remain static for the duration of their usage as SQL host variables, for example, they must not belong to any of the files in the current LS/LR/LU."

In Vol's example the variables clearly do belong to files in the current LS. It'd probably never go wrong in the example (as it stands) because both file vars are used only for equality tests - but how clear will it be to some newbie maintenance programmer that they can't change the tests in that part of the WHERE clause without invalidating the function!!

Regards, Richard

PS. Yes - the requirement for host variable values to remain static for the duration of the LS is, IMHO, a major misfeature in ProIV but I don't imagine it's going to get "fixed" anytime soon. :)
Nothing's as simple as you think

#9 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 02 January 2005 - 07:45 PM

Vol,

I rewrite the SQL as you have suggested and it seems fix the problem!!!!! Great!!! Thanks. 

Glad to be of help.

The only thing I have changed is that I put two ; instead of 3, :)

Ah, the dangers of typing code straight into the forum instead of ProIV. :)
Maybe Rob could fix the forum to validate all those parts of postings marked as "code" :D
Nothing's as simple as you think

#10 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 03 January 2005 - 02:55 AM

Thanks Richard,

Something puzzle me is that I write many other functions uisng the same style but they never give me problem. This function is the only exception so far as I know <_<

Regards,

Vol

#11 Los Rouse

Los Rouse

    Newbie

  • Members
  • Pip
  • 7 posts
  • Gender:Male
  • Location:Birmingham, England

Posted 04 January 2005 - 01:48 PM

As a matter of interest...

On the mainframe it is good practice to use file variables for database calls where possible.

The reason being the PRO IV file layout can be generated from DB2 tables which means the variables precisely match the size/format of the columns. If a scratch variable is defined that doesn't match, say is larger, than the column then the optimiser index or tablespace scans when it might not need to. A big performance killer.

I believe this 'feature' has been fixed in DB2 v8.

#12 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 05 January 2005 - 04:16 AM

[QUOTE]
PS. Remember also there is no proper distinction in Oracle between '' and NULL, I don't know if that might have been muddying the waters also.

Gents

Not sure whether this is connected, but for NULLS, you can use the NVL function.

e.g. Instead of using

...
WHERE
column_name = '' OR
column_name IS NULL
...

try

...
WHERE
NVL(column_name, ' ') = ' '
...

Not sure whether this will work with Pro-IV, but can't see why not.

#13 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 06 January 2005 - 02:21 PM

In our oracle conversions/imports, we found that pro-iv stores a single ' ' for character fields in oracle columns when it is an empty field. The only columns that allow nulls are pro-iv dates, stored as oracle dates.

#14 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 06 January 2005 - 05:18 PM

pro-iv stores a single ' ' for character fields in oracle columns when it is an empty field.

Yup - ProIV has to substitute a single space for the empty (zero length) string when it writes data to Oracle character columns - precisely because the empty string is emphatically not the same thing as a NULL value and yet Oracle fails to properly make that distinction. (I guess Oracle blew it in some early implementation and have had to retain the misfeature for backward compatibility).

The only columns that allow nulls are pro-iv dates, stored as oracle dates.

If you're saying ProIV can only handle NULL values in the Oracle DATE column type, I don't think that's true at all.
As far as I know, ProIV should handle NULLs in any Oracle column - but I say that as someone who hates NULLs in SQL and ProIV and who always tries to prohibit them.. so I don't actually have code that handles them :)
Nothing's as simple as you think

#15 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 07 February 2005 - 10:04 AM

Just read this post and had to make a comment on Richard's comment about using scratch variables...

My advice to one and all is *NEVER* *EVER* use scratch variables unless you absolutely have no other way of doing what you're trying to do. They cause all kinds of problems like obfuscating code, spelling errors in scratch variable names can make bugs impossible to track down, they clutter up the code needlessly and people can be scared into not removing them even when they're not really getting used anywhere... and basically because there's no requirement to define them anywhere in Native, so you can abuse them to your heart's content. And people do, because they can.

Consider this code I was searching through the other day:

In ARNE of file 1, LS#01 : $PARM = SOME_FILE_VARIABLE

Then, the only other use of the scratch variable anywhere, deep in LS#03, is in the interface to a global function.

So I stare at this interface and say to myself - what the hell is $PARM?????? and then have to go hunting for $PARM to work out what's going on here. If the interface had used the file variable, then I would have understood at once!

So they're bad. Scratch variables that is...



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users