How do I move my Ingres database to another location?

Document ID : KB000055516
Last Modified Date : 14/02/2018
Show Technical Document Details

This document can be used to relocate an existing Ingres database.

eTrust Admin has embedded eTrust Directory and an Ingres database.

This document is also applicable to the stand alone versions of eTrust Directory.

This document is applicable to Ingres II r2.6 and Ingres R3.

eTrust Admin 8.1 SP1 has embedded eTrust Directory 8.0.xxx and Ingres II 2.6
eTrust Admin 8.1 SP2 has embedded eTrust Directory 8.1.xxx and Ingres r3

In other words, to relocate CHECKPOINT, JOURNAL and DUMP locations of Ingres database. The document itself only shows an example of how to relocate CHECKPOINT location but the same instructions can be followed for JOURNAL and DUMP locations.

The sample scenario begins with creating a test database for practical purpose only (to understand the process) Users comfortable to work on an existing database, can skip the 'createdb' and 'ckpdb' portion of the document.

NOTE:

  1. To extend (not relocate) the DATA location, please use 'dxextenddb' command.

  2. This document is written mainly for eTrust Admin customers but the same concept is true for any eTrust product that embeds eTrust Directory & Ingres.

Syntax for relocatedb

relocatedb dbname -new_ckp_location=locationname | -new_jnl_location=locationname | -new_dump_location=locationname

dbname

Specifies the name of the database whose files are to be moved.

-new_ckp_location= locationname

Specifies the name of the new checkpoint location. The location must be defined with checkpoint usage. Checkpoints should not be run when relocating a checkpoint location.

-new_dump_location= locationname

Specifies the name of the new dump location. The location must be defined with dump usage. Checkpoints should not be run when relocating a dump location.

-new_jnl_location= locationname

Specifies the name of the new journal location. The location must be defined with journal usage. Journaling and checkpoints should not be run when relocating a journal location.

Solution:

Sample scenario

Note 1:
Steps 1 and 2 outline the necessary steps to create an alternate location (physically on disk and logically within Ingres) that can be used as target in the relocate step 4.

Note 2:
Step 3.1 and 3.2 describes the necessary steps to create a test database for practical purposes in this sample. In a production environment; steps 3.1 and 3.2 are not necessary; one would use the production database name in step 3.3

  1. Create the target location on disk for the checkpoint folder by creating a directory:
    C:\>mkdir newckp
  2. Utilize the accessdb program to create an Ingres Location.
    2.1 Run the accessdb command to launch the ACCESSDB program (see figure 1)
    C:\>accessdb
    Figure 1

    Figure 1

    2.2 Select " Locations " (press Shift+F2 ) to open the Location Information screen (see figure 2 below):

    Figure 2

    Figure 2

    2.3 To create a new location select " Create " (press Shift+F1 ) to open the Create a Location pane (see figure 3 below):

    Figure 3

    Figure 3

    2.3.1 Fill in/change the fields:

    Location Name: This can be ANY name, in this sample ckp_on_c_drive.

    Location Can Be Used For:

    Area: This is the folder directory as created on disk in step 1, in this sample c:\newckp

    Databases: change y (default) to n

    Checkpoint:
    change n (default) to y

    In this sample the fields would look like figure 4 below.

    Figure 4

    Figure 4

    2.3.2 Select " Save " (press F3 ) to Save and return to the Location Information screen; Here you will see the newly created location (in this sample ckp_on_c_drive; see figure 5)

    Figure 5

    Figure 5

    2.4 Exit the accessdb program by pressing F10 twice.

  • For the purpose of practical test this step shows how to create a test database on a QA or Development system using the createdb command; that can later be used in step 4 to relocate

    Step 3.1 and 3.2 describes the necessary steps to create a test database for practical purposes in this sample.

    3.1 In this sample the test database mydb will be used in the actual relocation:
      C:\>createdb mydb
      Creating database 'mydb' . . .
        Creating DBMS System Catalogs . . .
        Modifying DBMS System Catalogs . . .
        Creating Standard Catalog Interface . . .
        Creating Front-end System Catalogs . . .
      Creation of database 'mydb' completed successfully.
    
    3.2 Create a checkpoint for this database by using the command ckpdb:
    C:\>ckpdb mydb
    3.3 Run the 'infodb' command against the test database to check the location that is being used for the checkpoint. At the bottom of the output you will see:
        ii_database                   ROOT,DATA      
    D:\Program Files\CA\Ingres [EI]\ingres\data\default\mydb
        ii_journal                       JOURNAL         
    D:\Program Files\CA\Ingres [EI]\ingres\jnl\default\mydb
        ii_checkpoint                 CHECKPOINT   
    D:\Program Files\CA\Ingres [EI]\ingres\ckp\default\mydb
        ii_dump                         DUMP              
    D:\Program Files\CA\Ingres [EI]\ingres\dmp\default\mydb
               ii_work                          WORK              
    D:\Program Files\CA\Ingres [EI]\ingres\work\default\mydb
    
    3.4 Figure 6 shows the default checkpoint created on the D drive:

    Figure 6

    Figure 6

  • Perform the actual relocation

    Important Note: eTrust Admin users would be performing the actual relocation with the etrustadmin database.
    4.1 Use the relocatedb command

    C:\>relocatedb mydb -new_ckp_location=ckp_on_c_drive

    4.2 Run 'infodb' to verify that the new location in effectively being used. At the bottom of the output you will see:
    ii_database                    ROOT,DATA     
    D:\Program Files\CA\Ingres [EI]\ingres\data\default\mydb
        ii_journal                        JOURNAL        
    D:\Program Files\CA\Ingres [EI]\ingres\jnl\default\mydb
        newckp                         CHECKPOINT   c:\newckp\ingres\ckp\default\mydb
        ii_dump                         DUMP              
    D:\Program Files\CA\Ingres [EI]\ingres\dmp\default\mydb
              ii_work                          WORK             
    D:\Program Files\CA\Ingres [EI]\ingres\work\detault\mydb
    
    Compare the 'infodb' command output from step 3.2 with the above output to verify the change in location for checkpoint.

    4.3
    Optionally you may use explorer:

    Figure 7 shows that the location was moved to C drive.

    Figure 7

    Figure 7

    Figure 8 shows that the location no longer exists on the D drive:

    Figure 8

    Figure 8
  • Final Note: the test database was created for the purpose of this sample; this test database mydb can be removed by using the destroydb command; Destroy the test database that you created:

    C:\>destroydb mydb