Method 3: Reconfiguration with export/import

1. The table data is exported to a temporary file. The name of this file is R.<table name><company number> , for example, R.tfgld106590.
2. The table is dropped.
3. The table is created according to the new data dictionary definition.
4. The data is imported from the temporary file into the new table.
5. The indexes are created.

After successful completion, the temporary file is deleted.

To get a detailed report about the actions that are required to update the database, see the description of the -A , analyze mode, option.

These options are available with bdbreconfig:

• -A package combination

  This turns analysis mode on. The analysis mode of bdbreconfig helps in diagnosing performance bottlenecks when upgrading to a newer version of LN. It can also be used during development to gain insight in the resulting reconfiguration process and can help to prevent expensive conversions. This option is intended to be used from the command line.

  When running in analysis mode, no actual reconfiguration is performed, ever. The analysis mode only analyses the differences and produces a report (on stderr, redirect using the -e command line option). This report lists all the alterations that must be made to the database and other actions. Actions such as export or import, that must be performed to upgrade the system. See the description of the -c option (check-only); -A implies -c.

  The -A option accepts a package-combination argument and can be used on the command line in several ways to specify source and target database configurations:

  1. Use -A once. The analysis is between the current database configuration (source) and the specified package combination (target).
  2. Use -A twice. The analysis is between the package combinations you specify. The first package combination you specify is the source configuration, the second one is the target.
  3. Use -A once, combined with the -p option. The package combination that you specified behind -p is the source. The package combination that you specified behind -A is the target.

  The resulting report shows the database actions that are required to upgrade from the source configuration to the target. The report shows this information:

  • The changes, such as adding/removing columns, changing the characteristics of a column, and adding/removing indexes.
  • The consequences of those changes.
  • The exact SQL statements that are required by the database to apply the changes.

  Many changes can be performed entirely by the database (Oracle, DB/2, etc.).

  For example, extending the length of text string is a change that is fast on Oracle, independent of the number of rows in the table. But some changes require a full table export/import, for example adding a character-constraint to a field; the constraint-check is performed by the bdbreconfig program when it imports the data.

  For every detected change, a line is printed in the report that details the specific change. This is followed by an indication of the required reconfiguration method for that specific change. When a single table has multiple changes, the "heaviest" change decides which upgrade method is chosen for that table.

  The report makes this visible. You can specify tables for analysis in the normal way, see the description of the -N and -I options. If you do not specify any tables, bdbreconfig attempts to find all tables that exist in the original package combination and reports on them all. This code is an example invocation of a bdbreconfig analyze run:

  bdbreconfig6.2 -m -c -A b61a3 -A b61a8 -C 0
  …
  bicit331000  No reconfiguration is required
  bicit332000  No reconfiguration is required
  bpmdm000000  Reconfiguration is required
               Column 'izbh' is new (ENUM). ALTER TABLE required.
               Column 'rstr' is new (ENUM). ALTER TABLE required.
               Column 'tefp' is new (ENUM). ALTER TABLE required.
               ORACLE: EXECUTE: ALTER TABLE details omitted 
               
  bpmdm001000  Reconfiguration with export/import is required.
               Column 'bano' is new (STRING(34)). ALTER TABLE required.
               Column 'cedt' is new (DATE). ALTER TABLE required.
               Column 'mail' (TSS_STRING(100)) changed length (from 50 to 100). ALTER TABLE required.
               Column 'mail' changed from single byte to multibyte. ALTER TABLE not possible.

  This code shows a bdbreconfig invocation without explicit table names. Therefore a full report of all tables is created. In this case there are almost 4000 tables; the resulting output is over 15.000 lines. Many tables are unaltered (no reconfiguration is required).

  The bpmdm000 table has some new columns. All can be added by issuing database commands to Oracle. The exact statement that is required is shown in the full report, omitted here for readability. The exact statements for DB/2, Oracle, and so on are printed in the report. The reconfiguration of table bpmdm000000 is fast because the database can handle it.

  The next table is bpmdm001000. The first line shows that a full export/import is required for this table. A few columns are new which could be handled by the database. But the 'mail' column changed both length and type, single-byte to multibyte. Therefore a data conversion is required (ALTER TABLE not possible), leading to a full export/import. That is shown on the first report line for the table.

  To find potential performance bottlenecks, study the report looking for tables that require a full export/import and look for the reason. For example:

  cisli107000  Reconfiguration with export/import is required.
               Column 'brid' (STRING(9)) has new adjustment 'LEFT' in domain.
               Data conversion required.
               ALTER TABLE not possible due to domain constraints or data conversions

  This code shows that a string is now explicitly left-adjusted. That means that any leading spaces that may be in the database must be removed. Therefore, the entire table is exported and imported. The adjustment can be an unintentional side-effect of assigning a domain to the column. Dropping the adjustment saves an export or import operation. Every time you see Data conversion required, the change triggers the export/import operation. As another example:

  cprpd210000  Reconfiguration with export/import is required.
               Column 'dsca' (TSS_STRING(30)) changed adjustment (from 'NONE' to 'LEFT').
               Data conversion required.
               ALTER TABLE not possible due to domain constraints or data conversions (see above)

  The column has changed to a left-adjusted type, therefore leading spaces must be removed. This is a typical example of a change that requires an export/import.

• –C company numbers

  The tables are reconfigured in the companies that are specified by the –C option. You can specify the company numbers in these ways:

  • Specific company numbers, for example: –C 001 002.
  • Range of company numbers, for example: –C 001-010.
• –c

  With this option, the bdbreconfig tool displays, for each table and company, if reconfiguration is required and displays which reconfiguration method the tool uses. The actual reconfiguration is not performed.

• –D DBCM deployment name

  Repair data for upgrade to specified DBCM deployment.

• –E file

  Redirects errors to error file file.

• –F file

  Creates a list of table names and company numbers for which you must run refint to fix the reference counters after bdbreconfig is finished. This list is stored in file. For older versions of the porting set, you had to run refint on all tables that were reconfigured. The –F option generates the minimum set of table names. That is: a table is only added to the list if the reconfiguration affects data that is actually used by reference counters.

  When you run refint, you must specify the resulting file using the -s -I file options. In this way, refint only fixes the tables that are listed in file.

  Using the –F option of bdbreconfig can significantly speed up the reconfiguration process.

• –I file

  Reconfigure all tables whose names are listed in file. Each table name in file must be on a separate line. A table name can include a company number. For example, table tccom100 in company 570 is specified as tccom100570. If a table name does not include a company number, the table is reconfigured for the company numbers that are specified by the –C option. If the table does include a company number, this table is reconfigured for this specific company. The company numbers that are specified by the –C option are ignored.

• -J number of parallel tasks

  Specifies the number of parallel tasks that bdbreconfig executes. Every reconfiguration operation on a single table is a "task". Parallel execution can speed up the reconfiguration of many tables. The minimum valid value is 1, sequential behavior, the default is 4, the maximum is 128. Be careful when selecting this value: too much parallelism can cause system overload and database connection problems.

  You can also set the parameter using the bdbreconfig_parallel resource value or using a BDBRECONFIG_PARALLEL environment variable.

  Analysis (-A) and check-only (-c) modes execute faster due to parallelism.

• –M size

  Specifies the maximum size of the dump file. If the maximum file size is reached, a new file with a sequence number is opened and filled. The size can be specified as any number (bytes) or a number followed by K, M, or G, for kilobyte, megabyte, or gigabyte, respectively. The maximum size is 2 GB.

• –m

  Disable domain constraints. If you use this option, the bdbreconfig tool can choose a more optimal reconfiguration method. After the table is reconfigured, the data in the table can violate LN domain constraints.

• –Ntable[table ...]

  A list of tables to be reconfigured. A table name can include a company number. For example, table tccom100 in company 570 is specified as tccom100570. If a table name does not include a company number, the table is reconfigured for the company numbers that are specified by the –C option. If the table does include a company number, this table is reconfigured for this specific company only. The company numbers that are specified by the –C option are ignored.

• –p package combination

  The name of the package combination to be used.

  When combined with -A, the -p combination is used as the source configuration.

• –R file

  Create a report file that describes, for each table and company, the result of the reconfiguration.

• –r rows/transactions

  Defines after which number of inserted records, a commit is performed. The default value is 100. A number less than 100 is changed to 100.

• –s

  Suppresses error messages and other information.

• –T

  Log elapsed times in the report file.

• –t directory

  The directory name for temporary dump files.

• –U/-u

  Usage information.

• –V/-v

  Version information.

• –Z

  Reorganize the table. If you specify this option, the tables are not converted but only recreated. With this option, you do not require a data dictionary file with the .new extension. If a data dictionary file with the .new extension exists, the file is ignored. To recreate the table:

  1. The table data is exported to a temporary file.
  2. The table is dropped.
  3. The table is recreated.
  4. The data is imported from the temporary file.
  5. The indexes of the table are created.
• –Z -i

  Rebuild all indexes of a table. The tables are not affected, only the indexec are dropped and recreated.

  With this option, you do not require a data dictionary file with the .new extension.

bdbreconfig –Ntimcs016 –C001

bdbreconfig –Ntimcs016 –C000-010

bdbreconfig –N ttadv100222+

bdbreconfig –Ntimcs016 –C000-003

<tablename><compnr><space><status code><space><error><space><message text><newline>

dbtst100112 R0 0
dbtst100113 R1 2430 Reconfiguration failed (error 2430)
dbtst100114 R0 506
dbtst120112 R1 205 Insert failed (error 205)
dbtst120113 R0 506
dbtst120114 R0 506
dbtst160112 R2 114 Reconfiguration failed (error 114)
dbtst160113 R0 506
dbtst160114 R0 506