This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Nov 1, 2012 8:17 PM by 936666 RSS

JDBC + Cache Group Insertion Problem

936666 Newbie
Currently Being Moderated
Hi,

QUESTION 1:
--------------------
At TT END
command> SELECT COUNT(*) From oratt.Writetab;

COUNT(*)
-----------
2
-------------------------------------------------------------------------------------------------------------------------------------------------------
At ORACLE SQL END
SQL> SELECT COUNT(*) From oratt.Writetab;
COUNT(*)
-----------
2
-------------------------------------------------------------------------------------------------------------------------------------------------------
At TT END
command> INSERT INTO writetab VALUES (103, 'WWW.COM');
command> SELECT COUNT(*) From oratt.Writetab;
COUNT(*)
-----------
3
-------------------------------------------------------------------------------------------------------------------------------------------------------
At ORACLE SQL END
SQL> SELECT COUNT(*) From oratt.Writetab;
COUNT(*)
-----------
3
-------------------------------------------------------------------------------------------------------------------------------------------------------

So, If you insert from JDBC i am not able to insert in Timesten

Java code something as
Connection Conn = DriverManager.getConnection("jdbc:timesten:dsn=DataBaseName");
PreparedStatement PrepStmt = Conn.prepareStatement("insert into oratt.Writetab values (104,'test'));


SQLException: java.sql.SQLException: [TimesTen][TimesTen 11.2.2.2.0 ODBC Driver][TimesTen]TT2206: Table oratt.Writetab not found -- file "comp.c", lineno 2808, procedure "sbCompCacheTbl()"

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION 2:
--------------------

At TT END

Update oratt.writetab set pk=10 where pk=101
1000: Cannot set primary key columns to different values
The command failed.

At Oracle END

Update oratt.writetab set pk=10 where pk=101

I am able to update here

Why and what is the reason i'm not able to update at TT end.

Thanks!
  • 1. Re: JDBC + Cache Group Insertion Problem
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Hi 933663,
    Java code something as 
    Connection Conn = DriverManager.getConnection("jdbc:timesten:dsn=DataBaseName");
    PreparedStatement PrepStmt = Conn.prepareStatement("insert into oratt.Writetab values (104,'test'));
    
    SQLException: java.sql.SQLException: [TimesTen][TimesTen 11.2.2.2.0 ODBC Driver][TimesTen]TT2206: Table oratt.Writetab not found -- file "comp.c", lineno 2808, procedure "sbCompCacheTbl()"
    What user do you use for connection? Try something like the following
    Connection Conn = DriverManager.getConnection("jdbc:timesten:dsn=DataBaseName", "oratt","your_PWD");
    Regards,
    Gena
  • 2. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Hey Thanks Gena,
    Now i am able to do that.
    Any idea about Question:2 !!!

    Thanks!
  • 3. Re: JDBC + Cache Group Insertion Problem
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    I've got a couple of ideas :)

    Documentation (http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13065/comp.htm) says:
    "...Once a row is inserted, its primary key columns cannot be modified, except to change a range index to a hash index..."

    Best regards,
    Gena
  • 4. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Thanks for Time Gena,

    Here is something i am unable to load and unload the data's


    Command> connect "dsn=MYDB";

    Connection successful: DSN=MYDB;UID=pleelakr;DataStore=D:\MYDB\MYDB;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=C:\Time
    sTen\TT1122~1\bin\ttdv1122.dll;LogDir=D:\MYDB;PermSize=400;TempSize=40;TypeMode=0;OracleNetServiceName=OracleDB;
    (Default setting AutoCommit=1)
    Command>

    create user cacheadmin identified by oracle;
    create user oratt identified by oracle;
    grant create session to oratt;

    /*All these are at ORACLE End*/
    sqlplus oratt/oracle

    Create Table TableA (Col1 samllint NOT NULL PRIMARY KEY,Col2 varchar2(50),Col3 timestamp default sysdate);

    INSERT INTO TableA VALUES (1, 'Hello');
    INSERT INTO TableA VALUES (2, 'How');

    GRANT SELECT ON TableA TO cacheadmin;
    GRANT INSERT ON TableA TO cacheadmin;
    GRANT UPDATE ON TableA TO cacheadmin;
    GRANT DELETE ON TableA TO cacheadmin;

    /*All these are at ORACLE End*/
    sqlplus / as sysdba

    GRANT SELECT ON oratt. TableA TO cacheadmin;
    GRANT UPDATE ON oratt. TableA TO cacheadmin;
    GRANT DELETE ON oratt. TableA TO cacheadmin;
    GRANT INSERT ON oratt. TableA TO cacheadmin;

    /*All these are at TT End*/

    ttisql

    connect "DSN= MYDB;UID=cacheadmin;PWD=oracle;OraclePWD=oracle";

    CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP Testcache
    FROM ORATT. TableA (Col1 samllint NOT NULL PRIMARY KEY,Col2 varchar2(50));


    ttisql dsn=TTrepdefectdb;
    Command> GRANT SELECT ON ORATT. TableA TO cacheadmin;
    Command> GRANT UPDATE ON ORATT. TableA TO cacheadmin;
    Command> GRANT DELETE ON ORATT. TableA TO cacheadmin;
    Command> GRANT INSERT ON ORATT. TableA TO cacheadmin;


    /*All these are at TT End*/

    SELECT Count(*) from ORATT. TableA;
    -----
    2
    INSERT INTO ORATT. TableA VALUES (3, 'are You');

    SELECT Count(*) from ORATT. TableA;
    -----
    3

    /*All these are at Oracle End*/

    Delete ORATT. TableA where Col1=1;
    -----
    SELECT Count(*) from ORATT. TableA;
    -----
    2

    /*All these are at TT End*/
    UNLOAD CACHE GROUP CACHEADMIN.Testcache;
    3 cache instances affected.

    LOAD CACHE GROUP cacheadmin.Testcache commit every 100 rows;
    3 cache instances affected.( BUT it should be 2 cache instances affected.)
    (even I tried call ttcachestop )

    Here it should load only 2 rows from oracle but it is again loading 3 rows even after “UNLOAD CACHE GROUP CACHEADMIN.Testcache;”

    Thanks!
  • 5. Re: JDBC + Cache Group Insertion Problem
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Have you done a commit after delete operation in Oracle DB?
    Delete ORATT. TableA where Col1=1;
    commit;
    Regards,
    Gena
  • 6. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Yes I did the Commit;
    My Question is say suppose if we have 3 rows in Oracle and in TT cache if i deleted 1 row in Oracle and inserted 5 rows in Oracle can i be able to get the new 5 rows in TT cache.


    Thanks!
  • 7. Re: JDBC + Cache Group Insertion Problem
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Yes I did the Commit;
    I suppose the problem was because of the primary key was updated, but I can be wrong. I'll check later today.
    My Question is say suppose if we have 3 rows in Oracle and in TT cache
    if i deleted 1 row in Oracle and inserted 5 rows in Oracle can i be able to get the new 5 rows in TT cache.
    Yes, you are able to get the rows. If you dont run DML in TimesTen level you should create Read only Cache group and specify autorefresh interval. Read only cache groups are used for transferring data from Oracle to TimesTen.
    AWT cache groups are mainly used for transferring data from TimesTen to Oracle.

    Regards,
    Gena
  • 8. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Thanks Gena,
    I'm wondering that after restarting my system i had no problem in unloaded cache group <cachegroupname> and load cache group <cachegroupname> every 1000 Rows.I tested Multiple Times i am good with it now. :)

    Say if i Insert 100k rows in TimesTen with JDBC it takes around 18 to 23 seconds to cache, same time it takes 110 to 123 sec to replicate it to oracle, same Insertion of 100k rows in Oracle with JDBC it takes around 70 to 80 seconds.
    so for replication it takes around double the time of insertion
    how to reduce the replication time
    so what is done at the Timesten end to reduce from 70 sec(Oracle) to 18 sec(TimesTen)


    Thanks!
  • 9. Re: JDBC + Cache Group Insertion Problem
    ChrisJenkins Guru
    Currently Being Moderated
    I would guess that the forst time you did the test maybe you had not started the replication agent?If thsi is not running your DML will work only on TimesTen and not get replicated to Oracle.

    I don't really understand the question 'so what is done at the Timesten end to reduce from 70 sec(Oracle) to 18 sec(TimesTen)'? What exactly are you asking? The reason this is so much faster is because you are using TimesTen; an in-memory database which is optimized for high performance by using memory optimised data storage layout, in-memory optimised algorithms for data access and manipulation and probably direct access mode to eliminate IPC between the application and the database (TimesTen).

    The replication time is usually limited by the Oracle database; we can only push data to Oracle as fast as Oracle can accept it. If you have a complex workload with multiple tables being updated in TimesTen then you may see better replication performance by enabling parallel AWT (i.e. setting CacheAWTParallelism > 1). You should also check that your log buffer is large enough in TimesTen; the default size is too small for a heavy write workload.

    Chris
  • 10. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Hey Chris,
    how to enable parallel AWT (i.e. setting CacheAWTParallelism > 1). where i see this parameter and set the cache. How to check current CacheAWTParallelism settings
    ChrisJenkins wrote:
    If you have a complex workload with multiple tables being updated in TimesTen then you may see better replication performance by enabling parallel AWT (i.e. setting CacheAWTParallelism > 1). You should also check that your log buffer is large enough in TimesTen; the default size is too small for a heavy write workload.
    You should also check that your log buffer is large enough in TimesTen;
    For my database i have set 400 PermSize + 40 TempSize
    how to set LogFileSize + LogBufMB

    What is the purpose of TempSize and LogFileSize and LogBufMB

    how to check my current databases log buffer and how to increase log buffer

    Thanks!
  • 11. Re: JDBC + Cache Group Insertion Problem
    ChrisJenkins Guru
    Currently Being Moderated
    All this information can be found in the documentation. If you are planning to use TimesTen for anything serious then you really do need to get a good understanding of it by studying the documentation.

    The parameters CacheAWTParallelism, LogFileSize, LogBufMB atre all DSN attributes just like PermSize and TempSize. They are set in the sys.odbc.ini file (Unix/Linux) or via the ODBC Data Sources admin tool in Windows.

    You can check the current parameter values that are in effect by calling the builtin procedure ttConfiguration from ttIsql after connecting to the datastore.

    TempSize sets the size of the memory region used to store temporary objects such as temporary tables, locks, materialised result sets, sort space etc.

    LogBufMB sets the size of the log buffer used to stage transaction log (redo/undo) records between memory and disk and is a key tunable for performance. LogFileSize sets the size of individual trnsation log files. When a log file reaches this size it is closed and a new fiel started.

    You can monitor the log buffer effectiveness by looking at the fields LOG_BUFFER_WAITS and LOG_FS_READS in the table SYS.MONITOR while you run a test workload. If either these fields are non-zero and increase over time then your log buffer is too small.

    Chris
  • 12. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    Hi,
    I have inserted 150k Rows from TimesTen and it is been replicated successfully from TimesTen to my Oracle DB.I checked no of rows in TimesTen and Oracle ,showing same rows as 150K rows.After Some time i see in TimesTen some 30k rows are missing. i did not stop any of my process suddenly after 1 hour i see there is mismatch in rows.But in Oracle it is showing as 150k rows only.What is the reason here.
    The missing rows are in middle(i.e from 3000 to 33000 rows are missing since i had primary key i was able to check that these rows are missing in TimesTen)

    Do i need to flush all the data in TimesTen and reload from Oracle? if so why i need to do.


    Thanks!
  • 13. Re: JDBC + Cache Group Insertion Problem
    ChrisJenkins Guru
    Currently Being Moderated
    The only way that rows could 'vanish' from TimesTen like this yet still be present in Oracle is (a) if you have data aging configured for the cache group (the rows may have been aged out) or (b) if you have executed some UNLOAD CACHE GROUP operation. TimesTen does not 'lose' rows for no reason.

    Chris
  • 14. Re: JDBC + Cache Group Insertion Problem
    936666 Newbie
    Currently Being Moderated
    The only way that rows could 'vanish' from TimesTen like this yet still be present in Oracle is (a) if you have data aging configured for the cache group (the rows may have been aged out) or (b) if you have executed some UNLOAD CACHE GROUP operation. TimesTen does not 'lose' rows for no reason.
    I have not set any thing
    how to check that i have configured data aging for the cache group
    the rows may have been aged out
    If so how in between rows will be aged out.

    there is no possible for (b) even i unload cache group i will load cache group <cachegroupname>commit every N rows.

    Thanks!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points