Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741

    Unanswered: Implicit conversion

    I'm not a regular DB2 user but one of the things that I like about DB2 is its safe typing in SQL code when compared to Oracle or Microsoft SQL Server. So I was surprised to see that IBM are promoting implicit conversion as a "feature" in 9.7. http://www-05.ibm.com/ch/events/symp...osium_2009.pdf

    Doesn't anyone else think implicit conversion is actually a step backwards? Is there a way to turn it off?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I guess the implicit casting is there for better Oracle compatibility.
    As far as I know they simply follow the same rules as Oracle now, to make porting easier.

    I do like strict checking as well. Postgres for example took the other direction when moving from 8.2 to 8.3. Their rules are a lot stricter now.

    If DB2 really applies the same logic as Oracle, I don't see a big problem though. For my personal taste, the implicit casting in Oracle is good enough.

  3. #3
    Join Date
    Nov 2009
    Location
    Dusseldorf, Germany
    Posts
    3
    Hallo everybody,

    dportas, I agree and I also wonder if it's possible to switch off implicit casting. An example for one of the reasons: lately we faced some performance problems because of implicit casting in this statement (DB2 LUW 9.7.4):

    SELECT
    wmwg.stich_datum,
    wmwg.isin AS isin,
    wmwg.mandant AS mandant,
    case
    when wmwg.gd213 IN ('36','38') then
    coalesce(emit240.bp_id,emit245.bp_id)
    else
    coalesce(emit245.bp_id,emit240.bp_id)
    end as bp_id

    from
    abssd06t wmwg

    LEFT OUTER JOIN abssd17t emit240
    on wmwg.stich_datum = emit240.stich_datum
    and wmwg.mandant = emit240.mandant
    and wmwg.gd240 = emit240.emit_nr

    LEFT OUTER JOIN abssd17t emit245
    on wmwg.stich_datum = emit245.stich_datum
    and wmwg.mandant = emit245.mandant
    and wmwg.gd245 = emit245.emit_nr

    where ...


    The columns are defined like this:

    EMIT_NR CHARACTER(6)

    GD240 INTEGER
    GD245 INTEGER

    In addition we have an index for ABSSD17T consisting of EMIT_NR and STICH_DATUM.

    For the above statement, explain output looks like this:

    ...
    Left Outer Nested Loop Join
    | Access Table Name = TD01HTDE.ABSSD17T ID = 44,4
    | | #Columns = 1
    | | Compressed Table
    | | Avoid Locking Committed Data
    | | May participate in Scan Sharing structures
    | | Scan may start anywhere and wrap, for completion
    | | Fast scan, for purposes of scan sharing management
    | | Scan can be throttled in scan sharing management
    | | Relation Scan|
    | | Prefetch: Eligible
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 3
    Left Outer Nested Loop Join
    | Access Table Name = TD01HTDE.ABSSD17T ID = 44,4
    | | #Columns = 1
    | | Compressed Table
    | | Avoid Locking Committed Data
    | | May participate in Scan Sharing structures
    | | Scan may start anywhere and wrap, for completion
    | | Fast scan, for purposes of scan sharing management
    | | Scan can be throttled in scan sharing management
    | | Relation Scan|
    | | Prefetch: Eligible
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 3
    Return Data to Application
    | #Columns = 4

    If we change the statement to

    SELECT
    wmwg.stich_datum,
    wmwg.isin AS isin,
    wmwg.mandant AS mandant,
    case
    when wmwg.gd213 IN ('36','38') then
    coalesce(emit240.bp_id,emit245.bp_id)
    else
    coalesce(emit245.bp_id,emit240.bp_id)
    end as bp_id

    from
    abssd06t wmwg

    LEFT OUTER JOIN abssd17t emit240
    on wmwg.stich_datum = emit240.stich_datum
    and wmwg.mandant = emit240.mandant
    and substr(char(wmwg.gd240),1,6)= emit240.emit_nr

    LEFT OUTER JOIN abssd17t emit245
    on wmwg.stich_datum = emit245.stich_datum
    and wmwg.mandant = emit245.mandant
    and substr(char(wmwg.gd245),1,6) = emit245.emit_nr

    where
    ...


    the explain output looks like this

    Left Outer Nested Loop Join
    | Access Table Name = TD01HTDE.ABSSD17T ID = 44,4
    | | Index Scan: Name = TD01HTDE.ABSSD17I01 ID = 2
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: EMIT_NR (Ascending)
    | | | | 2: STICH_DATUM (Ascending)

    | | #Columns = 1
    | | Compressed Table
    | | Avoid Locking Committed Data
    | | Evaluate Predicates Before Locking for Key
    | | #Key Columns = 2
    | | | Start Key: Inclusive Value
    | | | | | 1: ?
    | | | | | 2: 2011-09-30
    | | | Stop Key: Inclusive Value
    | | | | | 1: ?
    | | | | | 2: 2011-09-30
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    Left Outer Nested Loop Join
    | Access Table Name = TD01HTDE.ABSSD17T ID = 44,4
    | | Index Scan: Name = TD01HTDE.ABSSD17I01 ID = 2
    | | | Regular Index (Not Clustered)
    | | | Index Columns:
    | | | | 1: EMIT_NR (Ascending)
    | | | | 2: STICH_DATUM (Ascending)

    | | #Columns = 1
    | | Compressed Table
    | | Avoid Locking Committed Data
    | | Evaluate Predicates Before Locking for Key
    | | #Key Columns = 2
    | | | Start Key: Inclusive Value
    | | | | | 1: ?
    | | | | | 2: 2011-09-30
    | | | Stop Key: Inclusive Value
    | | | | | 1: ?
    | | | | | 2: 2011-09-30
    | | Data Prefetch: None
    | | Index Prefetch: None
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | #Predicates = 1
    Return Data to Application
    | #Columns = 4

    Means in the second case the index is used, in the first case it is not. So I'd like to re-ask deportas' question:

    Is it possible to switch off implicit casting?
    Could the above problem have been avoided in another way?

    Kind regards!
    Last edited by stratmf; 11-17-11 at 07:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •