1 2 Previous Next 20 Replies Latest reply: Nov 1, 2012 10:17 PM by 936666 RSS

    JDBC + Cache Group Insertion Problem

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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-Oracle
                          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
                            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-Oracle
                              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
                                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-Oracle
                                  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
                                    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