Originally posted by: SystemAdmin

What is the proper syntax in mdq for tx to bind oracle variables?

In a dblookup or dbquery statement we use the bind() construct.

In mdq, should this be followed?

The documentation does not state how to set this up in a mdq.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I have found that :bind works for variables in the mdq.

So far it seems that only variables, or hard coded literals (not fields) can be bound.

I am researching whether fields are automatically bound (like the =: construct in plsql).

This is extremely important to perform as well as other tools!

A dynamic sql statement must be completely bound, or it will not be cached.

Has anyone tested binding in general to see that ascential datastage tx generated/loaded does use the =: syntax (and/or results in bound code)?
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

CJS,

We've been able to bind DB2 variables using DBLOOKUP, and it did work. When viewing the run time stats, the query that was being passed to DB2 was "select fields from this_table where other_field =? for fetch only". So DB2 still prepared the statement but was able to read the variables from cache instead doing the query one by one. This cut processing time in half.
How were you able to bind variables in the MDQ? Can you please share your format?
Thanks,

Ed
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

CJS,

We've been able to bind DB2 variables using DBLOOKUP, and it did work. When viewing the run time stats, the query that was being passed to DB2 was "select fields from this_table where other_field =? for fetch only". So DB2 still prepared the statement but was able to read the variables from cache instead doing the query one by one. This cut processing time in half.
How were you able to bind variables in the MDQ? Can you please share your format?
Thanks,

Ed
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

In the mdq, I placed the :bind() syntax within the where clause around variables passed at run time. I didn't need to use the syntax with other DB Columns (like pl/sql would).
It is reassuring to hear your results.
I am optimizing some difficult SQL code and have not seen the performance gain (yet!)

Here is the where clause from the MDQ...

WHERE sla.warehouse_id =:bind(64)
and sha.header_id in (select header_id from so_headers_all
where last_update_date > TO_DATE(:bind(#LAST_EXEC_RUN#),'YYYY-MM-DD HH24:MI:SS.')
union
select header_id from so_lines_all
where last_update_date > TO_DATE(:bind(#LAST_EXEC_RUN#),'YYYY-MM-DD HH24:MI:SS.')
union
SELECT order_header_id
FROM so_picking_headers_all
WHERE last_update_date > TO_DATE(:bind(#LAST_EXEC_RUN#),'YYYY-MM-DD HH24:MI:SS.')
UNION
SELECT spha.order_header_id
FROM so_picking_headers_all spha,
wsh_deliveries wsi,
wsh_departures wsd
WHERE spha.delivery_id = wsi.delivery_id
AND wsi.actual_departure_id = wsd.departure_id
AND wsd.last_update_date > TO_DATE(:bind(#LAST_EXEC_RUN#),'YYYY-MM-DD HH24:MI:SS.'))
AND SHA.HEADER_ID = SLA.HEADER_ID
AND SHA.ORDER_TYPE_ID = SOTA.ORDER_TYPE_ID
AND SLA.PARENT_LINE_ID = SLA2.LINE_ID(+)
AND SLA.LINE_ID = SLd.LINE_ID(+)
AND SLD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND SLD.WAREHOUSE_ID = MSI.ORGANIZATION_ID(+)
AND sld.line_id = spla.ORDER_LINE_ID(+)
and sld.warehouse_id = spla.warehouse_id(+)
and sld.inventory_item_id = spla.inventory_item_id(+)
AND nvl(SPLA.PICKING_HEADER_ID,1) > bind:(0)
AND SHA.HEADER_ID = SPHA.ORDER_HEADER_ID(+)
AND ((SPHA.PICKING_HEADER_ID = (SELECT MAX(SPHA2.PICKING_HEADER_ID)
FROM SO_PICKING_HEADERS_ALL SPHA2
WHERE SPHA2.ORDER_HEADER_ID = SHA.HEADER_ID))
or (nvl(SPHA.PICKING_HEADER_ID,sha.HEADER_ID) = SHA.HEADER_ID))
AND SPHA.DELIVERY_ID = WSI.DELIVERY_ID(+)
AND WSI.ACTUAL_DEPARTURE_ID = WSD.DEPARTURE_ID(+)
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange