12 Replies Latest reply: Jul 20, 2012 9:02 PM by 948372 RSS

    TimesTen JDBC Connection , is it a TCP/IP connection?

    948372
      Hi Gurus,

      I connection to TimesTen as follow:
      String url = "jdbc:timesten:direct:dsn=my_ttdb";
      String username = "ADMINISTRATOR";
      String password = "";
      Connection connection = DriverManager.getConnection(TestTT.url, TestTT.username, TestTT.password);


      I wonder if this JDBC Connection is a TCP/IP connection on the backend?

      If this is not a TCP/IP connection, how can I make a connection with TimesTen via TCP/IP protocol?


      ---------------------------------------------------------
      For more info:

      dsn:my_ttdb configuration likes:

      [my_ttdb]
      Driver=/home/tttest/TimesTen/tt1122/lib/libtten.so
      DataStore=/home/tttest/ttdata/database/my_ttdb
      LogDir=/home/tttest/ttdata/logs
      PermSize=40
      TempSize=32
      DatabaseCharacterSet=ZHS16GBK
      OracleNetServiceName=orcl

      Edited by: 945369 on 2012-7-17 下午11:06
        • 1. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
          Chrisjenkins-Oracle
          This is a non-IPC direct mode connection (note the 'direct' in the JDBC connection string) and will provide much better performance than a TCP connection. If TimesTen and your application are on the same machine this is the recommended connection type. Why do you want a TCP connection?

          If you really need a TCP connection then you should check the TimesTen documentation regarding configuring client/server connectivity and the TimesTen JDDBC documentation on the changes needed to the connection string.

          Chris
          • 2. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
            948372
            Thanks for your answer, Chris.


            "If TimesTen and your application are on the same machine this is the recommended connection type."

            There will be several Cache Grids (each grid consists of more than 2 cache nodes) on different machines for my application.
            Data in Oracle will be loaded into each cache group on the start up of my application.
            Each cache group will contain different data without overlapping.

            So I cannot assure the application will be on the same machine with the TimesTen database.

            If they are on different machines, which connection type would be the recommended? This connection type "direct" or "TCP/IP" or something else?

            Sorry for these dumb questions.
            Thank you again, Chris!

            Edited by: 945369 on 2012-7-18 上午2:26
            • 3. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
              Chrisjenkins-Oracle
              Do you really plan to have multiple separate cache grids? Or do you mean one grid with many nodes? The idea behind grid is that an application can just connect to it's local node but still access all the data in global cache groups (data is moved to where it is needed or processed globally in parallel depending on query type, options etc.).

              If you have to connect to a TT datastore on a different machine then of course you will need to configure TimesTen client/server and use that (over TCP). Of course, then every database access will pay the cost of multiple network round trips so the overall performance will be reduced compared to non-IPC local direct mode access. This may not matter depending on your overall performance requirements.

              Chris
              • 4. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                948372
                Hi Chris, thanks for the reply!

                Yes, we are planning to have multiple Cache GRIDs.

                OK, I wanna say more about my application and the details.

                We are working on a Anti-Fraud platform, this would be a real time trading platform. We need to design carefully for reducing the latency for transactions.

                For using TT, we are going to use it to save all of the transactions details in 180 days. That would take huge memory as we calculate.
                So multiple Cache GRIDs will be required. In our application, we are going to have 4 grids, each of them save data for a different area.
                Further more, each GRID will contain 4 (or more) cache nodes.
                In one word, we want our application can be scale-out.

                In the backend Oracle, the data should be in one big table with all the data, even if the data is out of 180 days. (We only need 180 days' data in TT for our calculation needs.)

                We want to reach this effect:
                Cache Grid A, loads all of the data in Oracle for area A;
                Cache Grid B, loads all of the data in Oracle for area B;
                The same with cache grid C and D.

                Again, cache grid A could have 4 cache nodes, we hope TT can help us to make the coherence between these 4 cache nodes in cache grid A. Is this possible?
                The same with cache grid B, C and D.

                The forehead application in written in Java, when a transaction reaches our platform, we will identify which area it belongs to.
                Then, (this is the important problem I want to make sure), if it belongs to A, I need to insert a new record to cache grid A.
                And I don't want to specify which node (in cache grid A) to insert this record.

                Is this possible?
                It seems that I need to use java to connect to a Cache GRID, and I can only find examples for connecting to a specify cache node (a database defined by DSN).

                I am reading the IMDB User's Guide, I've read 6 chapters, but I still didn't find the answer.

                Thanks a million in advance!

                Edited by: 945369 on 2012-7-18 上午5:17
                • 5. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                  Chrisjenkins-Oracle
                  Thanks for that information. it makes it much easier to understand your use case and therefore easier to offer meaningful advice.

                  The principle of cache grid is that it allows you to create a global AWT cache group (dynamic or static). The data in thie cache group is distributed across all the nodes in the grid. TimesTen guarantees that for any element of data (table row) there is at most one copy of the data in the whole grid (there may be no copies if that row has not been loaded from Oracle). When a row is referenced by a SQL statement running in a particular grid node (say node A) then if that row is not present in that node then it will be brought to that node from either (a) another grid node (if it is present in the grid) or (b) from Oracle (if it is not present in the grid and this is a dynamic global cache group - for static cache groups we will only fetch the row from another grid node). TimesTen maintains (duplexed and distributed) ownership/location information about all data in the grid in order to be able to do this.

                  Thus an application that is connected to any node of the grid can access data that is 'anywhere in the grid' (static cache groups) or 'anywhere in the grid or in Oracle' (dynamic cache groups) as long as the SQL it is executing is 'dynamic load capable'. Also, for many types of query (and some DML) you can leverage 'global processing' where a query submitted at one node is executed in parallel across all nodes of the grid and the result returned as for a normal query.

                  Grid co-ordinates updates (i.e. insert/update/delete) to AWT tables in global cache groups across all the grid members so you can insert/update/delete at any node in the grid and consistency and transactional integrity both within the grid and between the grid and Oracle are guaranteed.

                  So, in your case you can insert data at any node in Grid A and that data will (a) be propagated to Oracle and (b) will be accessible from any node in Grid A.

                  Some things to bear in mind if you plan to use Cache Grid:

                  1. Only some kinds of SQL query / DML are 'dynamic load capable'. You should check the SQL you plan to issue to ensure it will behave as expected in a grid environment.

                  2. Only some kinds of query (and very little DML) can leverage global processing so agai nchck if this is an issue for you.

                  3. What level of availability do you need from the grid? If you need a high level of availability then each 'grid member' should actually be a TimesTen active-standby replicated pair not a standalone TT database. If you use standalone grid members and one fails it will disrupt grid processing for a period of time until recovery actions have been taken.

                  4. Managing a grid in operation is complex. If you are using a replicated grid (as mentioned in (3)) then you should use Oracle Clusterware to manage and operate the grid. This automates almost all aspects of grid deployment, operation, failover management, recovery etc. and makes things relatively simple. You cannot use Clusterware to manage a non-replicated grid. Building your own monitoring and management infrastructure for a grid (replicated or not0 is a significant development effort and I would not recommend it!

                  Chris
                  • 6. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                    948372
                    Chris, thanks a lot for so detailed advices!

                    I will learn all items you teacher mentioned and give them shots one by one.

                    I'm afraid that I will come back to ask you teacher for helps again, :)

                    Thanks agin and have a nice day!
                    • 7. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                      948372
                      I am not sure it is suitable to post the following questions in this thread.

                      I just want to make sure whether I am on the right track.

                      I want to test the "One cache grid with two grid nodes, and the data is distributed into these two nodes".
                      This is the procedures I did:

                      0. connect "dsn=my_ttdb; uid=xx; pwd=xx; oraclepwd=xx"
                      1. call ttgridcreate('mygrid');
                      2. call ttcachestart; call ttrepstart;
                      3. create AWT cache group:

                      create asynchronous writethrough global cache group awt_mytable from
                      cacheadm.mytable (id number(6) not null,
                      my_date date not null,
                      amount number(10) not null,
                      location varchar2(10) not null,
                      type number(2) not null,
                      primary key (id))
                      AGING USE my_date LIFETIME 15552000 SECONDS CYCLE 1 SECONDS ON;

                      4. call ttgridattach(1, 'mygrid', 'localhost', 9991);
                      5. load cache group awt_mytable where location = 'SH' commit every 1 rows;
                      6. select * from mytable; returns 3 (sample) records.

                      #1 --> #6 have been done on the 1st grid node.

                      The following commands are being done on the 2nd grid node.
                      7. connect "dsn=my_ttdb2; uid, pwd; oraclepwd";
                      8. call ttcachestart; call ttrepstart;
                      9. create AWT cache group use the same statment as #3.
                      10. call ttgridattach(1, 'mygrid', 'localhost', 9992);
                      11. select * from mytable;
                      I want to see the global cache group can be accessed in any node in the cache  grid. But it returns no data.

                      11. autocommit off;
                      CALL ttOptSetFlag('GlobalProcessing', 1);
                      12. select * from mytable;
                      It returns 3 records, the same as #6.

                      My Question:
                      a. If I insert into thousands of records into node1, will part of the data go to node2 automatically?
                      b. If yes, how can I make sure there are data and which data on node2 actually?
                      c. I config an odbc dsn client, use this dsn to connect to my TT server, I connect to the 1st node, and I want to search the data that I inserted into the 2nd node, but the result is I cannot get the data in node2, can only get the data in node1. How to do global query with Java?

                      Regards,
                      Wenbin
                      • 8. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                        948372
                        I think I missed the configuration for replication between these two TimesTen data stores.

                        I am trying to add the replication scheme.
                        • 9. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                          Chrisjenkins-Oracle
                          Grid does not use replication. You cannot setup replication between two grid members - its not required nor allowed. The only use of replication with grid is for HA. Each grid member can be an active-standby pair (and indeed that is strongly recommended for any production deployment).

                          Back to your previous question(s):

                          a. If I insert into thousands of records into node1, will part of the data go to node2 automatically?

                          A1: No. All the data will initially reside in the node where it was inserted (and will be propagated to Oracle of course). Data may then move to other nodes as it is accessed at those nodes. If you connect to node 2 and execute a query of the form 'select * from mytable where id = n' (where n is the id of a record that was inserted in node 1) then that row will be moved to node 2 and the query will return it. if you then do 'select * from mytable' or 'select count(*) from mytable' at node 1 you will see that the record is no longer there. Remember that only certain types of query are 'grid capable'. Basically, grid capable queries are those which use full key equality predicates on defined primary or foreign keys in cache group tables. For more detail see the Cache User's Guide in the section on Dynamic Load.

                          b. If yes, how can I make sure there are data and which data on node2 actually?

                          A2: The answer was No; see A1 above.

                          c. I config an odbc dsn client, use this dsn to connect to my TT server, I connect to the 1st node, and I want to search the data that I inserted into the 2nd node, but the result is I cannot get the data in node2, can only get the data in node1. How to do global query with Java?

                          A3: Cache grid provides limited support for 'Global Queries' These are queries which will be executed across all nodes in the grid to return a result (normally queries are executed against only the data currently present in the local node where the query is being executed). Global query only supports SELECT and UNLOAD operations. Ther are other restrictions too (as documented in the Cache User's Guide):

                          The query must reference exactly one table.
                          - The query cannot include a self join, a derived table or subqueries.
                          - The query cannot reference a global temporary table.
                          - The query cannot be performed on the standby database of an active standby grid
                          member.
                          - ROWNUM and GROUP BY clauses cannot be used in the same query.
                          - The query cannot be used with GROUPING SETS, CUBE, ROLLUP, GROUPING,
                          GROUPING_ID, or GROUP_ID.
                          - The query cannot include the WITH clause.
                          - The query cannot include analytic SQL functions.
                          - The PassThrough connection attribute must be set to 0.

                          The execute a query as a global query you need to set the optimiser hint 'GlobalProcessing' to one. To do this you can do the following using JDBC or any other TimesTen API.

                          1. Ensure that AutoCommit is disabled on your connection (this is anyway best practice but is essential if using optimiser hints).

                          2. Execute the SQL statement 'call ttOptSetFlag('GlobalProcessing',1);

                          3. Using a PreparedStatement object, prepare the required query. The statement will be prepared using the currently active optimiser hints (global processing in this case).

                          4. Commit (this clears all active optimiser hints).

                          5. Execute the prepare query. It will be executed as a global query.

                          More (and important) details on all this can be found in the comprehensive TimesTen documentation.

                          Chris
                          • 10. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                            948372
                            Chris, thanks for your reply.

                            I am still in the trouble.

                            For this question:
                            a. If I insert into thousands of records into node1, will part of the data go to node2 automatically?
                            Your answer is:
                            A1: No

                            If the data that I insert into cache group in node1, does not go to cache group in node2 automcatically (node1 and node2 are in the same grid, and both of them has an identical cache group), how to understand this sentence you mentioned in an earlier reply:
                            --------
                            The principle of cache grid is that it allows you to create a global AWT cache group (dynamic or static). *The data in thie cache group is distributed across all the nodes in the grid*
                            --------

                            and how to understand this sentence in IMDB User's Guide:
                            --------
                            A cache grid is a set of distributed TimesTen in-memory databases that work together to cache data from an Oracle database and guarantee cache coherence among the
                            TimesTen databases.
                            --------


                            What I did:
                            1. Create a grid, named mygrid;
                            2. Attach a database: my_ttdb1 to mygrid by calling ttgridattach(1, 'mygrid', 'localhost', 9991)
                            3. Create global awt cache group cachegroup_1 in my_ttdb1
                            4. Load cache group to cachegroup_1

                            5. Attach the other database: my_ttdb2 to mygrid by calling ttgridattach(1, 'mygrid', 'localhost', 9992) [I need to describe it clearly: my_ttdb1 and my_ttdb2 are in the same machine, I just make 2 ODBC, and connect to each of them];
                            6. create global awt cache group cachegroup_2 in my_ttdb2;
                            7. I did not load cache group to my_ttdb2.

                            Then I assume:
                            I have a grid now: mygrid;
                            There are two grid nodes: my_ttdb1 and my_ttdb2;
                            Each of them has a cache group, the two cache groups are exactly created by the same defination;
                            If I insert data into my_ttdb1, I can use "select" to see these data in the other node: node2 in my case; (but in fact, I cannot select those data in my_ttdb1 unless I set golbal processing to 1);

                            If I insert very very many records to my_ttdb1, and it cannot hold so many data in itself, so the IMDB cache should help me for the coherence between these two nodes, so I want to see the data go to other grid nodes in the same cache grid.
                            Only in this case, I can attach more grid nodes to the cache grid if the data keep getting more and more.

                            Could you please give me some advice on my problems?
                            Or please give me some links to pdf files or other resources, from where I can learn these skills.

                            Thank you very much, Chirs.


                            Regards,
                            Wenbin
                            • 11. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                              Chrisjenkins-Oracle
                              Hi Wenbin,

                              With regard to my statement: The principle of cache grid is that it allows you to create a global AWT cache group (dynamic or static). The data in this cache group is distributed across all the nodes in the grid.

                              What I was trying to say (and maybe my choice of words was not as precise as it could have been) is that unlike normal cache groups, which are completely local to a single TimesTen datastore and have no knowledge of anything outside of themselves (other than the backend Oracle database), global cache groups are aware of their peers in the other nodes in the grid. Data in a global cache group is shared across all the members in the grid. The actual distribution of data across the members depends on the access pattern. Data is only moved between grid members when it is referenced or commands are executed to move it; it is not moved/redistributed/rebalanced automatically.

                              With regard to the statement in the documentation: A cache grid is a set of distributed TimesTen in-memory databases that work together to cache data from an Oracle database and guarantee cache coherence among the TimesTen databases.

                              Cache coherency does not mean/imply that data is somehow uniformly/automatically distributed. What it means is that there are not multiple inconsistent copies of the data within the cache. A reference to any particular piece of data will return the same (correct) values regardless at which grid member the reference occurs. Cache grid guarantees this.

                              If you wish to insert a lot of data into the grid and have it distributed over all the grid members then you have several options:

                              1. Have multiple inserters inserting data into different grid members concurrently.

                              2. Insert into just one grid member and periodically execute commands at other grid members to move data to those grid members (for example LOAD CACHE GROUP ... WHERE ...).

                              3. Use LRU based aging to 'age out' older inserted rows from the member where you are inserting. These rows wil lthe nby dynamically reloaded into another node when/if they are referenced there. Caution: aging is a low priority background activity. if the insert rate is high and sustained aging may not be able to discrd data as quicly as it is inserted and the database may still become full.

                              Remember, Cache grid is just that a cache grid not a standalone grid database., The concept here is that the 'master of everything' is the Oracle DB and the cache grid is just cached data.

                              Chris
                              • 12. Re: TimesTen JDBC Connection , is it a TCP/IP connection?
                                948372
                                Hi Chris, MANY MANY THANKS!!

                                Thanks for your so detailed clarification, now I know that I miss-understand your words and the words read from User's Guide.
                                And I will try one of your methods: insert into different grid member with multiple inserters, or maybe I will try "round-robin" with one inserter to insert data into all the cache members.

                                You are such a nice teacher! Again, thank you!

                                Have a nice day.


                                Best Regards,
                                Wenbin