SAP MII SQL Data Source connection Returns backdated (old) data.

[Former Member]

Dear Experts,

I have a datasource connection in SAP MII to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Driver Name	:  SYSTEM_DRIVER
SQL Engine	: Vendor SQL

Isolation Level : Default

JDBC Version

:    1x (without XA support)

Driver Class Name : oracle.jdbc.OracleDriver

Database URL      : jdbc:oracle:thin:@<servername>:<PROT>:<DBSID>

User Name         : <Username>

Password          : <Password>

On query execution, the data returned is old. It takes about 12 hours for the new data to be reflected.

I am using a fixed query. Query cashing disabled.

Please advise.

Regards

Maaz

[Former Member]

Hi Saumya,

I tried with the command query but the results are same. I still get the delay.

Also I have a output that is selected so I need to use the fixed query with output.

Both 'Allow Buffering' and 'Use Query caching' are unchecked.

Hi Swaroop,

No time periods/schedule selected in both insert and select queries. As evident from the query above, the dates are passed through the params and not the query start and end dates. Passing the dates as SD and ED is creating problems with the i5Grid. Guess this makes a different discussion.

If refering to the select query, as mentioned earlier, the select query instantly reads a change inserted by another query correctly. Both the procedures are part of the same package.

Please help. Considering raising a CSS but is this a product issue or a programming one?

Regards

Maaz

[swaroop_anasane]

Thanks Maaz,

I didn't notice the query that closely. Are the client and server system on same time zones?

If not, you might want to check it in this dimension.

Regards,

Swaroop

[Former Member]

Hi Swaroop

Yes they are on the same timezone. On the same area/network.

The page behaves exactly the same when accessed on the server.

Regards

Maaz

[swaroop_anasane]

Hi Maaz,

Also, just check if you incidently have selected any time period or schedule.

Thanks,

Swaroop

[jcgood25]

Did you trace the query string on the database side?  Do you get the same results when requesting the same SQL natively on the Oracle DB side?  Unless you have date logic in the select statement, MII more or less simply passes the query to the DB.

[Former Member]

Hi Jeremy,

Yes I have done that and it gives the current data. I have done the following tests.

Insert Data from sqlplus

select on sqlplus gives the new record

select in MII query does not show the inserted record.

Insert data from MII query

select on sqlplus gives the new record

select in MII query gives the new record

I think there is some sort of cashing being done in MII.

[jcgood25]

Please share the SQL being used.  Having MII return data as expected when the insert was done by an MII insert query, but not when the insert was done natively doesn't make much sense if the query is the same (unless syntax or caching issues - which would only be in the template properties)

[Former Member]

Hi Jeremy

     This issue has been troubling quiet a bit. The following are the queries I am using.

For insert:

/*==================== insertUpdateLoadData ====================*/

  PROCEDURE insertUpdateLoadData(

    iId NUMBER

    ,iPurordid NUMBER

    ,iSplrdid NUMBER

    ,iMatgroupid NUMBER

    ,iHaulerId NUMBER

    ,iTruckid NUMBER

    ,iDriverid NUMBER

    ,iInvoicenum VARCHAR2

    ,iChangedby VARCHAR2

    ,Ret OUT ZNCursor

  ) IS

  x NUMBER;

  err_code VARCHAR2(15);

  err_msg VARCHAR2(200);

  BEGIN

    IF iId > 0 THEN

      BEGIN

        UPDATE tbl_matrcpt_loaddata

        SET inuse='N'

        WHERE id=iId;

        INSERT INTO tbl_matrcpt_loaddata(

          id

          ,purordid

          ,splrid

          ,matgroupid

          ,haulerId

          ,truckid

          ,driverid

          ,vouchernum

          ,invoicenum

          ,changedby

          ,changedon

          ,inuse

        )

        VALUES(

          iId

          ,iPurordid

          ,iSplrdid

          ,iMatgroupid

          ,iHaulerId

          ,iTruckid

          ,iDriverid

          ,TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI')

          ,UPPER(iInvoicenum)

          ,iChangedby

          ,SYSDATE

          ,'Y'

        );

        COMMIT;

      END;

    ELSE

      BEGIN

        INSERT INTO tbl_matrcpt_loaddata(

          id

          ,purordid

          ,splrid

          ,matgroupid

          ,haulerId

          ,truckid

          ,driverid

          ,vouchernum

          ,invoicenum

          ,changedby

          ,changedon

          ,inuse

        )

        VALUES(

          seq_matrcpt_loaddataid.nextval

          ,iPurordid

          ,iSplrdid

          ,iMatgroupid

          ,iHaulerId

          ,iTruckid

          ,iDriverid

          ,TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI')

          ,UPPER(iInvoicenum)

          ,iChangedby

          ,SYSDATE

          ,'Y'

        );

        x:=sql%rowcount;

        COMMIT;

        OPEN Ret FOR

        SELECT 'Success' AS Status, to_char(x)||' record(s) added' AS Message FROM dual;

      END;

    END IF;

    EXCEPTION WHEN OTHERS THEN

      err_code := SQLCODE;

      err_msg := SUBSTR(SQLERRM, 1, 200);

      OPEN Ret FOR

      SELECT 'Error' AS Status, err_code+err_msg AS Message FROM dual;

  END insertUpdateLoadData;

/*================= End of insertUpdateLoadData ================*/

For select

SELECT

  a.id

  ,a.purordid

  ,a.splrid

  ,b.ponum

  ,a.matgroupid

  ,d.grpname

  ,a.haulerId

  ,a.truckid

  ,a.driverid

  ,a.vouchernum

  ,a.invoicenum

  ,a.isfrstwt

  ,e.weight as fstwt

  ,e.remark as fstrem

  ,a.issecwt

  ,f.weight as secwt

  ,f.remark as secrem

  ,abs(coalesce(e.weight,0)-coalesce(f.weight,0)) as netwt

  ,a.ismatwtassn

  ,a.isgrnpstd

  ,matrcpt_isscrapbypo(b.id) as isscrap

FROM vw_matrcpt_loaddata a

LEFT JOIN vw_matrcpt_purord b ON a.purordid=b.id

LEFT JOIN vw_matrcpt_matgroup d ON a.matgroupid=d.id

LEFT JOIN vw_matrcpt_weight e ON a.id=e.loadid AND e.weighttyp=1

LEFT JOIN vw_matrcpt_weight f ON a.id=f.loadid AND f.weighttyp=2

WHERE a.changedon between to_date('[Param.1]','YYYY-MM-DD HH24.MI.SS')

and to_date('[Param.2]','YYYY-MM-DD HH24.MI.SS')

AND a.isfrstwt like '[Param.3]'

AND a.issecwt like '[Param.4]'

AND a.isgrnpstd like '[Param.5]'

ORDER BY a.changedon desc

The insert from the query above takes about 5 mins to reflect changes when results are queried. A select in the sqlplus returns the inserted row instantly.

Though a similar insert in the weight table gives the right output instantly.

In both cases the select query is the same. The query templates are copies in both the insert cases.

Regards

Maaz

[saumya_govil]

Dear Maaz,

Can you try changing the insert query to 'Command' query?

Also, check if the 'Allow Buffering' flag is turned off along with the 'Use Query caching' flag in the query object.

Regards,

Saumya Govil