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?

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.

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!