This post is a “living” document – it will be added to over time to provide everything you need to know about administering the Oracle server to host Btrieve data.

What Objects are created into Oracle?

1. For tables with at least one index which was not unique before and the table does not have a Primary Key defined in the DDFs:
   1. MDS_RECNUM column is a primary key. It is added as the last segment to any index which is not already unique. Thus, all indexes are created as Unique. This guarantees proper record traversal forward and backwards through otherwise duplicate values.
   2. A sequence ( <tablename>_S0 ) is used to increment the MDS_RECNUM value. The INSERT command uses sequence.NEXTVAL to set the field value.
2. If the table has a primary key and has indexes which are not unique, the segments of the primary key are appended to the non-unique key to make it unique.
3. AutoInc Btrieve fields
   1. A sequence ( <tablename_S<fieldnum> ) is used to increment the unique value. The INSERT command uses sequence.NEXTVAL to set the field value.
4. Optional Trigger
   • If there are inserts into the tables outside of the Mertech driver, the primary key (autoinc/mds_recnum) must be set properly…
   • There is also an “inverse” key concept – this is used for descending and case-insensitive index segments.
   • The trigger is used to set these values when outside access is done on the tables.
     • Named as <tablename>_T
5. “Application Locks”
   • Btrieve allows an application to lock records outside of a transaction. This is counter-intuitive to a SQL database and conflicts with normal transaction processing. Therefore, the standard server record locks cannot be used. Instead, the driver utilizes DBMS_LOCKS package and manages its own record locks.
   • Locks are acquired through dbms_lock.request and individual locks are released with dbms_lock.release.
   • When a table is closed or a transaction is ended, a large number of locks may be needed to be released. This falls to the mds_release_locks_v1 stored procedure.

   The migration tool will create the needed stored procedure upon first attach provided the correct password is given for the user “SYS” when requested. However, the Oracle administrator will typically want to create the objects manually. This can be done with the following script run as the user “SYS”:

   CREATE OR REPLACE PROCEDURE mds_release_locks_v1
   (p_lockList IN varchar2, p_numReleased OUT PLS_INTEGER)
   AUTHID DEFINER AS
       -- p_lockList is a comma separated list of lock IDs
       -- p_numReleased returns the number of locks that released successfully
   
       lockId varchar2(50);
       startPos int := 1;
       endPos int;
       lockStatus int;
       BEGIN
           p_numReleased := 0;
           while startPos <= length(p_lockList) loop
               -- parse lock list
               endPos := instr(p_lockList, ',', startPos);
               IF endPos = 0 then endPos := length(p_lockList)+1; end IF;
               lockId := substr(p_lockList, startPos, endPos - startPos);
               startPos := endPos + 1;
   
               -- Do the release
               lockStatus := dbms_lock.release(to_number(lockId));
   
               ---- Give some feedback
               --SELECT DECODE(lockStatus,
               --  0,'Released',
               --  3, 'Parameter Error',
               --  4, 'Not owned',
               --  5, 'Illegal Lock Handle')
               --  INTO lockStatus_s FROM dual;
               --dbms_output.put_line(lockId || ': ' || lockStatus_s);
   
               IF (lockStatus = 0) then
                   p_numReleased := p_numReleased + 1;
               end IF;
           end loop;
   
       END;  -- mds_release_locks_v1()
   
   GRANT execute ON mds_release_locks_v1 TO public
   
   DROP public synonym mds_release_locks_v1
   
   CREATE public synonym mds_release_locks_v1 FOR sys.mds_release_locks_v1

   
   

6. License counting view
• mds_session_info is created as a view against V$SESSION. Select rights on mds_session_info must be given to the user running the application but not to the underlying system table.

This script must also be created as the user “SYS” if you can’t provide the correct password to the migration tool when requested:

    CREATE OR REPLACE VIEW mds_session_info AS
    SELECT username, machine, terminal, module FROM v$session
       
    GRANT SELECT ON mds_session_info TO public
   
    DROP public synonym mds_session_info
   
    CREATE public synonym mds_session_info FOR sys.mds_session_info

Can I use different table spaces?

The migration tool provides options for Table and Index table space. You can migrate some tables to one tablespace and other tables to another tablespace. If more control (such as a separate tablespace for BLOBs) is needed, we suggest generating the SQL Create script; modify this as needed and running that in SQLPlus or another tool. Then, use the migration tool to copy the data.

Data files used?

1. For each Btrieve data file, a corresponding interface file is created. ‘filename.ext’ becomes ‘filename_ext.INT’. The INT file contains metadata used at runtime and will reside in the location where the original data file would have been located. The original Btrieve data file is no longer needed.
2. The Pervasive.SQL/Btrieve engine is not needed for Mertech database drivers to work in the application (only by the migration tool). The Pervasive.SQL engine can be shut down after the data migration is performed.

User access needed to Oracle database (Runtime)

1. roles: CONNECT, RESOURCE
2. System privileges: “SELECT ANY DICTIONARY“, “SELECT ANY TABLE“
3. User will also need “UNLIMITED TABLESPACE” or a proper quota set.
4. DBMS_LOCK package must be installed (it is by default)
5. GRANT EXECUTE ON “SYS”.”DBMS_LOCK” TO “<user>”
6. If application will be creating tables on the fly (temp tables for instance), the user will need object creation rights.

   Application logic can be altered to login as a different user for these temp tables.

Optional access

The driver makes some queries against V$PARAMETER to establish some internal parameters. If access to the system view is not available, default values will be used instead.

To retrieve the maximum number of cursors that can be open:

To retrieve the db_block_size value used for the storage calculation:

To retrieve the numeric characters defined at the server to be used as a separator:

User access needed to Oracle (Migration)

The user doing a migration will need a little more rights to the database than the user running the application at deployment.

1. BTR2SQL needs access to V$PARAMETER view to retrieve information for storage estimation. This is the query:
SELECT value FROM v$parameter WHERE name = 'db_block_size'




2. The user of course needs rights to create tables and indexes.

Determine licenses used

You can use the following query to see how many licenses are in use by the Mertech driver. If this number exceeds the count provided by the license file, the driver will fail to run.

Things to look for

1. B_CREATE and / or file deletion through OS calls.

   Many apps will create “temp” files for sorting and other short-lived purposes. Or, create new files each quarter/year.

   B_Create is supported; however the table will not be fully defined in SQL. This can be overcome with application modification to utilize either the MdsAddTable function or an INT file Template (discussed elsewhere).

   Instead of removing the temp file using an OS call, utilize MdsDropTable (or B_DROP_FILE).

   There is also a B_TRUNCATE_FILE API if the application simply wants to delete all the records. This is far faster than the usual Btrieve method of read/delete each record and avoids dropping the table which may have adverse affects on the defined tablespaces.

Can app control login?

B_SQL_LOGIN and B_SQL_LOGOUT allow the application to avoid the login dialog and fully hide the user credentials used for accessing the database. Documented in the SDK portion of the User Guide.

Can app execute queries?

B_SQL_* functions allow execution of SQL queries and results retrieval on the same connection as the driver. Documented in the SDK portion of the User Guide.

Working with the new data

Now that the Pervasive Control Center is gone, how do I look at the data on the server?

Oracle makes a tool called SQL Developer – http://www.oracle.com/technology/software/products/sql/index.html

There are also many tools available for purchase online. One such is Aqua Data Studio – http://www.aquafold.com/

Most of these tools let you execute queries, edit data, modify table structures, import/export, etc.

This entry was posted on Wednesday, December 16th, 2009 at 12:21 pm and is filed under BTR2SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.