FAQ Overview

2005

What should I do when I insert a record and find that the journal file is full?

There is only 1 journal file whose size is 4MB that DBMaker defaulted, so the only way to solve this problem is to increase the size of journal file. There are two ways to increase the size. One way is to increase the size of the journal file. The other way is to add the number of journal files. Of course, you can also increase both the size and the number.

Author: Sandy
Last update: 2016-01-29 04:22


Would you please give me some notes for DB design?

System Structure:

This requirement is configured almost same as others. You need to consider:

(1) Using 3-tier or Server/Client to reduce the loading of Database Server,

(2) Using Stored Procedure to reduce the unnecessary round-trips.

 

DB Server Configuration:

(1) Try to allocate 1/2 of physical memory size to DCCA for better    performance.

(2) Appropriately splitting up the tablespace files to different Disks.

(3) Leave the independent table with big size into a sole tablespace. Prevent the small tables intermingling with the big table.

 

 Schema Design:

 (1) Set up the Lock Mode to Row or Page? By default, we will use Page Lock,but in some OLTP circumstance, Row Lock might be a better choice. But it will depend the table schema, (its index, and size and SQL commands).

 (2) Use transaction by any chances. DBMaker uses auto-commit as its default. Users should know that it will have worse performance under this configuration.

 (3) Make a 70% or 80% fillfactor in table and index. Or row-migration or index split occurrence will degrade your performance.

 (4) Create an appropriate index as needed. By study, 40% or above indexes will never be used in DBMS. Make sure that you choose the right index to use in your system.

 AP Design:

 (1) Using Hint to choose the best execution plan if necessary.

 (2) Use DB_DTCLT and DB_ITIMO to kill the possible dead connection. (By Default, this feature is turn on.)

 

It's not easy to use a simple word to express the good design in DBMaker. All of above are all big topics. User could refer to our manual with respect to the Performance Tuning and likewise.

Author: Sandy
Last update: 2016-02-02 09:50


Why does ESQL/C make error when links to the EC program after compiled successfully in UNIX?

Because for Redhat9 or later versions, the libdmapic.a that is used to link the EC program needs to be recompiled under Redhat9. And we have built the new one for customer.

Author: Sandy
Last update: 2016-02-02 09:45


Is it possible to set the timeout in any JDBC setting?

Yes, you can use the following codes to achieve your purpose: 

Class.forName("dbmaker.sql.JdbcOdbcDriver");

Connection con = DriverManager.getConnection(

"jdbc:dbmaker://127.0.0.1:2453/dbsample4;CTIMO=30;","sysadm","");

Author: Sandy
Last update: 2016-02-02 09:53


Why do the error messages turn into garbage characters in Windows?

When users link their ODBC program, we suggest that the users link to the dmapi4X.lib. Otherwise, users maybe face the garbage characters error if the database was not well configed.

Author: Sandy
Last update: 2016-02-02 09:39


When and how does DBMaker use user order file (EBCDIC_stroke.ord)?

To use the user defined Order, user should put the order file they defined into $DBMaker_Home/shared/codeorder.
And then, use the DB_Order keywords to specify the order file name to let DBMaker use the new order they defined. 
The order file is only used when create Database;

Author: Sandy
Last update: 2016-02-02 11:09


Are there two DBMaker.bin processes started by default when we start database? 1. And when the user connects to Database, does the child process start one by one? 2. If the users want to check whether Database is alive or not, can they use the number of alive processes by PS command in LINUX? (If the number of DBMaker.bin process is under 2, Does it means that Database is dead?)

By default, when DBMaker is started, three processes started in Linux; one is the primary process by which we can access from AP, the other two are IO_Server (2) and Service_Server (1) separately. IO_Server services the I/O request and can’t be killed and Service_Server does the work of updating statistic information punctually and can be killed if unnecessary. So, when Database is started, at least two processes existed. If we connect to Database by dmSQL/C, another process (3) is started by the primary process to serve for the dmSQLC client.

Author: Sandy
Last update: 2016-02-02 11:17


How to rebuild DATABASE by shell script? Could you give me some sample scripts for it?

Please refer the ${dbmaker_home}\samples\DATABASE\init.sql.
CREATE DB DBSAMPLE4;
CREATE TABLE SYSADM.Card ( NumSERIAL(1) PRIMARY KEY,FirstName VARCHAR(30), LastName VARCHAR(30),TitleVARCHAR(30), BMp LONG VARBINARY) IN DEFTABLESPACE;
INSERT INTO SYSADM.Card VALUES (?,?,?,?,?) ;
1, 'Eddie', 'Chang', 'Manager', &data1.b0(0,6666);
2, 'Hook', 'Hu', 'Software Engineer', &data1.b0(6666,6666);
3, 'Jackie', 'Yu', 'Software Engineer', &data1.b0(13332,6666);
4, 'Ray', 'Sung', 'Software Engineer', &data1.b0(19998,6666);
5, 'Louis', 'Liu', 'Software Engineer', &data1.b0(26664,6666);
6, 'Trent', 'Clowater', 'Software Engineer', &data1.b0(33330,6666);
7, 'Oscar', 'Tseng', 'Software Engineer', &data1.b0(39996,6666);
8, 'Jerry', 'Liu', 'Manager', &data1.b0(46662,6666);
END;

Author: Sandy
Last update: 2016-02-02 11:21


Does DBMaker kill any active session forcibly When DBMaker DB is shut down by terminate DB command?

Yes, it kills any active session forcibly when DB is shut down by terminate DB command.

Author: Sandy
Last update: 2016-02-02 11:24


Is there any bug about this kind of SQL, and what kind of result shall we get from the following SQL statement? select * from (select * from t1 where a=1) a1 left join t2 b1 on (a1.a=b1.a) Is the following SQL different meaning from the above SQL? select * from t1 left join t2 on (t1.a=1 and t1.a=t2.a)

The SQL statement 'select * from (select * from t1 where a=1) a1 left join t2 b1 on (a1.a=b1.a)' and 'select * from t1 left join t2 on (t1.a=1 and t1.a=t2.a)' means different. In fact, the second statement just like 'select * from t1 left join t2 on t1.a=t2.a where (t2.a=1)', the 'on' condition only used for join, not for the selection filter of t1.

Author: Sandy
Last update: 2016-02-03 10:05


Does DBMaker have simple method in command line that can output the data to text file of csv type?

Yes, you can use JDATATransfer tools to achieve this purpose, please refer the usage of JDATATransfer. DBMaker will add this function into dmSQL in future versions.


 
     
  
  
  
  
  
  
  
  
  
  
 
 
 

Author: Sandy
Last update: 2016-02-03 10:16


How can I access DBMaker if I didn’t register the DSN?

If you didn’t register the DSN ,you should use indiscriminately the                                   following connection string when you use ADO:

            strConn="Driver=DBMaker4.1Driver;DATABASE=Dbsample4;SvAdr=192.168.0.164;PtNum=8888;UID=sysadm;Pwd=;ATCMT=1;"

 

  You need to pay attention to following proceedings:

  1. Don’t change the order of the keywords in the connection string.

  2. To the different Databases, you need to modify the values of the following  three parameters: DATABASE=Dbsample4; SvAdr=192.168.0.164; PtNum=8888

  3.To the different versions of DBMaker and DBMaker,you need to pay attention to the setting of the paramenter Driver=DBMaker 4.1 Driver ;following are the examples:

    eg:Access the DBMaker 4.1, set Driver=DBMaker 4.1 Driver;

        Access the DBMaker 4.2, set Driver=DBMaker 4.2 Driver;

        Access the DBMaker 4.1, set Driver=DBMaker 4.1 Driver;

        Access the DBMaker 4.2, set Driver=DBMaker 4.2 Driver;

Author: Sandy
Last update: 2016-02-03 10:15


How does the inside of the DBMaker deal with when insert BLOB data repeatedly? Does it share the BOLB data when the inserted BLOB data is same?

DBMaker will store only a single LO and share it between the tuples, If the same LO data is contained in many tuples. But that only occurred when UPDATA the dada. When INSERT data, DBMaker can’t assert whether the LO data should be shared. So it will store the LO data separately instead of sharing the data.

Author: Sandy
Last update: 2016-03-04 11:00


After dropping table and loading table, using update statistics table and update statistics separately; why the execute time of the latter is shorter than the former’s? Are the disposal methods in the DBMaker different between the two? And besides the data of the systablespace, what other systamcatalog will be updated when executing update statistics?

The disposal methods between the update statistics and update statistics table are different. Because that the update statistics will update all the table’s column, index and so on. So in order to save the resource, it is only update the statistics when there have accumulated certain amount data. As a result, the user finds that the update statistics takes less time.

User can use the following statement to force the DBMaker to update all the statistics:

            update statistics sample=100

The update statistics statement will update table,column,index. So all systables that include them will be updated.

Author: Sandy
Last update: 2016-03-04 11:02


What should the program do when calling the ODBC functions and the returned SQL code is SQL_ERROR or SQL_SUSSESS_WITH_INFO? Does it need error handling?

Yes, the program should do error handling. First, customer can use the SQLERROR() to      get the details about the error massage, Then use different methods to the different  program in different compute language. To the Java program use SQLException
or  SQLWarning to deal with, and to the VC program is different.

Author: Sandy
Last update: 2016-03-04 11:06


How to deal the error handling in VC program when there appear SQL_ERROR or SQL_SUCCESS_WITH_INFO?

It needs the program to do the estimation. If the SQLCODE=-1. It should write the
code to deal with the error handling. The sample code is too long to add it here, and if you want to get the detail of the sample code please connect with the DBMaker Support Team.

Author: Sandy
Last update: 2016-03-04 11:08


How to deal the error handling in VB program when there appear SQL_ERROR or SQL_SUCCESS_WITH_INFO?

It uses adodb.sqlerror object instead of err object to get the native errors from the          DBMaker for that err object can’t get the native error codes of the database.

  Following is the sample to do the error handling in VB program

// VB

       Dim objConn As New ADODB.Connection

    On Error GoTo ErrorHandle

  

    objConn.Open "dsn=dbsample4"

    objConn.Execute "insert into t1 values (2, '12345678901234567abcdefg')"

    If objConn.Errors.Item(0).NativeError = 63 Then 'success with info

        Err.Raise objConn.Errors.Item(0).NativeError, objConn.Errors.Item(0).Source,  objConn.Errors.Item(0).Description, objConn.Errors.Item(0).HelpFile, objConn.Errors.Item(0).HelpContext

    End If

    Set objConn = Nothing

 

ErrorHandle:

    Err.Raise objConn.Errors.Item(0).NativeError, objConn.Errors.Item(0).Source, objConn.Errors.Item(0).Description, objConn.Errors.Item(0).HelpFile, objConn.Errors.Item(0).HelpContext

    Set objConn = Nothing

Author: Sandy
Last update: 2016-03-04 11:10


What is the function of the FillFACTOR parameter when create table?

The FILLFACTOR feature optimizes the utilization of space for data pages by
reserving space for the expansion of existing records. When a record grows up and the page free space is not enough,some records will be put into other page, which affects the performance seriously.
So we set FILLFACTOR to avoid this.The principle is when the length of the records changes, the length of all the records won’t over the size of the page, avoiding some records will be put into other data page.

Author: Sandy
Last update: 2016-03-04 11:13


How to decide the value of the FILLFACTOR?

Deciding appropriate FILLFACTOR is very case-dependent. Here are some rules of thumb.

If a table fits one of the following criteria,  

set a larger FILLFACTOR for it:

1. The table rarely updates.

2. The record size is mostly fixed, ie, all columns are mostly fixed-length and not null. The FILLFACTOR of a table with fixed-size records can be set to 100.

3. Neither of above, but the frequently updated columns are largely fixed-length.

 

Here are the steps to estimate the lower bound. But please note that the calculation of actual usage of pages could become very complicated. The method provided here is to give a quick and effective way to estimate a range:

1. Calculate the record size Empty with all nullable columns null and variable-length columns empty.

2. Calculate the record size Full with all columns filled and in full length.

3. Then the FILLFACTORr should not be smaller than max(50, Empty/Full*100 )

For most cases the ideal FILLFACTOR will fall in between this value and 100, and you should be able to narrow down the range by calculating Empty depending on the real case. For example:

table1 {

     c1 int not null;

     c2 int;

     c3 char(20) not null;

     c4 varchar(12) not null;

}

In this case, Empty = 4 + 0 + 20 + 0 = 24, Full = 4 + 4 + 20 + 12 = 40, so the FILLFCATOR should be at least 24/40*100 = 60.

However, if c4 is restricted by AP to at least six characters (for instance, password), Empty should be 4 + 0 + 20 + 6 = 30, and the FILLFACTOR be not smaller than 30/40*100 = 75.

 

For BLOB/CLOB columns, use 8 bytes (size of OID) as their size.

Author: Sandy
Last update: 2016-03-04 11:14


How to change the file extension of SFO in PHP program?

Following is the sample:

$conn=odbc_connect("nba","SYSADM","");

   if($conn)

         {

              error_reporting(0);

              odbc_do($conn,"drop table php_fotest");

              error_reporting(7);

         $res1=odbc_do($conn,

                       "create table php_fotest(c1 int,c2 file)");

          if($res1){

                             $res=odbc_prepare($conn,

                                     "insert into php_fotest values(?,?)");

                    odbc_exec($conn, "set extname to 'aaa'");

                   $param[0]=1;

                   $param[1]="'rahim.jpg'";

                   $res2=odbc_execute($res,$param);

                   if($res2)

                        {

                         $res3=odbc_exec($conn,"select c1,filename(c2) from php_fotest");

                         $str1=odbc_result($res3,1);

                         $str2=odbc_result($res3,2);

                         echo "$str1\n";

                         echo "$str2\n";

                          echo "Show dbmaker system fo image file ";

                          echo "<IMG SRC=\"display2.php?id=1&dbuser='SYSADM'&dsn='nba'&dbpwd=''\"><p>";

                        }

                   }

        }

Author: Sandy
Last update: 2016-03-04 11:15


How to migrate the database from 3.x to 4.x?

You can use the Unload/load commend to implement the migration, i.e. first unload the database from 3.x, and then load it to the 4.x. following is the steps, take 3.7 and 4.2 databases for example:

  1. Backup the old database if necessary.
  2. In the dmSQL of DBMaker3.7 connected to the original database (we named it MIS), unload MIS database to a target directory, say C:\ 37MIS \.
  3. Copy all the Files in DB_FoDir into the directory C:\ 37MIS \.
  4. Create a new database; we named it NEWMIS, with DBMaker 4.2 with the same section configuration as MIS in dmconfig.ini but the different database directory.
  5. In the dmSQL of DBMaker4.2 connected to the NEWMIS database, load the old database from the target directory, say C:\ 37MIS \.
  6. End of migration job.
  7. Check if all the objects, including stored procedure, UDF, View still work in the new database and enjoy the new function of DBMaker 4.2.

Also, the migration database job needs patience and there are more notices, for the details about the database migration, please refer to the DBMaker Migration User’s Guide manual.

Author: Sandy
Last update: 2016-03-04 11:28


Can 4.1.4 and 4.1.6 coexist on one OS?

No, they can not coexist on the same OS. Otherwise, the ODBC driver of
the previous installed version will be rewritten by the last version.

Author: Sandy
Last update: 2016-03-04 11:20


Can DBLINK connect 4.14 and 4.16; is there any difference between DBLINK connecting same versions and DBLINK connecting different versions?

Yes, DBLINK can connect 4.14 and 4.16, i.e. there is no difference for DBLINK connecting whether the same versions or not.

Author: Sandy
Last update: 2016-03-04 11:21


If the db shutdown abnormally, does DBMaker do instance recovery?

If DB is abnormal shutdown, maybe the page buffer data will not write into data file. After the DB started,the DBMaker server will do a recovery. The recovery will check if the data file has abnormal transaction or not. If there is a transaction that isn't commit or rollback, server will do re-do and un-do to recovery the transaction to make the DB keep in consistency.

Author: Sandy
Last update: 2016-03-04 11:25


If there are several journal files and switch current journal file to next one, does the checkpoint occur?

Yes, when writing journal buffer into journal file (check point) and find the journal file does not have enough space, server will write the journal data into another journal file.
So, this behavior is in check point processing.

Author: Sandy
Last update: 2016-03-04 11:23


2006

When load the file which was unloaded from other DB (not DBMaker) in SQL script into DBMaker, what should be paid attention?

To load the file into DBMaker successfully, you should care the following points:

1. To set the extname of the file to .s0.

2. Make sure the max length of a line in s0 file do not over 80 characters. Use the escape character “\” to split 2 or more lines every 80 characters. And remember to handle double byte word.

Author: Sandy
Last update: 2016-03-07 03:27


When does the DBMaker create .s1 file while unloading file from a DB?

At present, The file size from DBMaker unloaded is about 1G,that’s limitation of DBMaker in dmSQL tool; So when DBMaker detects the exported file size near 1G it will create a new external data file for rest data until exporting all the data. And the rest file will be named as **.s1, **.s2 and etc.

Author: Sandy
Last update: 2016-03-07 04:23


After the server side has upgraded to 4.1.6 from 4.1.4, can the client side with 4.1.4 machine connect to the upgraded server?

Yes. The 4.1.4 Client can connect to the 4.1.6 Server smoothly.

Author: Sandy
Last update: 2016-03-07 04:31


After the server side has upgraded to 4.1.6 from 4.1.4, can the ODBC program linked with 4.1.4 work as well as before?

Generally, the ODBC program linked with the 4.1.4 static library (dmapi41.lib for windows, dmapic.a for Linux/Unix) can access the 4.1.6 server, but to enjoy the newly added functions and avoid the bugs fixed in 4.1.6 (Please see the 4.1.6 release notes for
details), we suggest that the ODBC program should be re-linked with the static
library (dmapi41.lib for Windows, dmapic.a for Linux/Unix) of 4.1.6.

Author: Sandy
Last update: 2016-03-07 04:33


After dropping table and loading table, using update statistics table and update statistics separately; why the execute time of the latter is shorter than the former’s? Are the disposal methods in the DBMaker different between the two? And besides the data of the systablespace, what other systamcatalog will be updated when executing update statistics?

The disposal methods between the update statistics and update statistics table are different. Because that the update statistics will update all the table’s column, index and so on. So in order to save the resource, it is only update the statistics when there have accumulated certain amount data. As a result, the user finds that the update statistics table takes less time.

User can use the following statement to force the DBMaker to update all the statistics:

            update statistics sample=100

The update statistics statement will update table,column,index. So all systables that include them will be updated.

Author: Sandy
Last update: 2016-03-07 04:36


How to confirm deamon of auto update statistics working(is there anything shown on process)?What is the trigger to let update statistics work(like how many records insert into table)?

The statistics daemon launch at 3:00 am automatically if the DB_SVRST = 1 is set on dmconfig.ini when database start up. Customers can view whether statistics daemon server is alive by select * from sysuser command; SERVICE_SERVER just is this. Statistics daemon only launch at 3:00 am, if you want to do update statistics at another time, you can use the schedule within the Operation System to implement it. update statistics table_name will update statistics value of table_name totally (default sample = 100).

update statistics daemon will update the statistics of tables following below rules:

  • table size doesn’t over 20 page size, doesn’t update statistics data
  • if insert data doesn’t over 2 page size, doesn’t update statistics data
  • the time is not over 10 days, doesn’t update statistics data.

Author: Sandy
Last update: 2016-03-07 04:37


What is the limitation to the number of the command handles when using JDBC data source to connect to DBMaker?

At present (DBMaster 4.16/4.20), DBMaker allows the maximum of the command handles is 257.  But after 4.21, 4.22, 4.3 and later version, customer can set a configuration keyword – db_mxcmd in server side to enlarge this limitation.  The default is 257, and the range is 1 ~ 32767.  If you set this keyword too big, DBMaster should allocate more memory internally.  Please consider your application used handle.  Allocating too many handles will influence the performance in OS or Database.

Author: Sandy
Last update: 2016-03-07 04:39


What can I do if the number of the command handles in my application over the limitation?

User should modify their application to prepare once and execute n times.

For example:

ps2 = this.conn.prepareStatement("insert into TESTDB values(?, ?, ?);"); 

         for(int i=0; i<list.size();i++)

{

                            tData = (TestData)list.get(i);

                            ps2.setString(1,tData.shp_cd);

                            ps2.setString(2,tData.shp_nm);

                            ps2.setString(3,tData.tel_no);

                                 ps2.executeUpdate();

                            this.conn.commit();

}

To improve performance, we suggest writing application as the above example i.e. PREPARES SQL statement just once and then EXECUTE it as many times as they need during the run of the program.

Author: Sandy
Last update: 2016-03-07 04:43


How does the DBMaker find free page if the present page is full when an insert command is executed?

DBMaker will keep previous inserted page id (hint page) internally, when next insert
SQL executes, DBMaker will find out this page to insert first, if the page space is not enough then it try to find out next page to insert. For example, if previous inserted page id is 200, and someone execute insert command, DBMaker will insert data at page 200 first, if the 200 page have no enough space, DBMaker will scan next page (201) to find free space for inserting by sequential. If still does not find out free space to insert till the end of tablespace, it will jump begin of tablespace to scan to find out free page.

Author: Sandy
Last update: 2016-03-07 04:45


When execute an update command that the new data is large than the previous data, how does the DBMaker do?

If the rest page space can contain new data, DBMaker will delete old data first then put new data in the same page position, if rest page space is not enough, DBMaker will delete old data first and set new data becoming an indirect data then. The indirect record needs more I/O and may cause performance down,to avoid generating indirect data, setting the proper FILLFACTOR when you creating table.

Author: Sandy
Last update: 2016-03-07 04:47


How does DBMaker deal with the delete command?

In DBMaker, every record has a structure, DBMaker just mark one of them to ''delete'’ flag, the data still exist in page (customer cannot see data anymore but for DBMaker the data still exist), it's for better performance, but in customer's view, the whole data had been deleted.

Author: Sandy
Last update: 2016-03-07 04:48


Why does the error occur sometimes when using cursor update in the ESQL application?

For example:

We have a table with schema:

dmSQL> def table card1;

create table SYSADM.CARD1 (

 NUM  SERIAL(1),

 DD  INTEGER not null,

 EE  CHAR(1) not null,

 FIRSTNAME  VARCHAR(30) not null,

 LASTNAME  VARCHAR(30) not null  ,

 TITLE  VARCHAR(30) default null ,

 BMP  LONG VARBINARY default null )

 in DEFTABLESPACE  lock mode page  fillfactor 100 ;

alter table SYSADM.CARD1 primary key ( NUM, DD, EE, FIRSTNAME);

Then write the ESQL/C code:

EXEC SQL declare scroll_cursor scroll cursor for

      select Num,dd,ee,LastName, FirstName from Card1

      where NUM < 9 order by EE,NUM, DD,FIRSTNAME 

      into :Num, :LastName :indvalue, :FirstName :indvalue

      for update of LastName;

The above ESQL/C code returned the error.

 

When use cursor for update, there are two limitations:

  1. The columns included in the ORDER BY clause must be the index.
  2. If there are multiple columns in the ORDER BY clause, the sequence of the columns must be same as the index key’s definition.

So the ORDER BY clause in the above ESQL/C code should be modified to:

Create an index with then same sequence with NUM, DD, EE, FIRSTNAME Order by NUM, DD, EE, FIRSTNAME

To keep same sequence with the index key’s definition.

 

Author: Sandy
Last update: 2016-03-07 05:07


The SQL manual says: Use two consecutive single-quotes to include a single-quote character in a quoted string. It means that if we want to insert a data included single quote, we should use another quote as the escape character, but in the following statement which is in a my ESQL program, why do not I need single quote as escape sequence but I can still execute the program successfully? (From DBMR1702, version: DBMaker 4.1.6) sprintf(sChnkbn, "'A");

If you use the following statement in ESQL, the single quote is seeing as escape sequence

EXEC SQL insert into 1702(a,b) values(1,'''A');

But your statement sprintf(sChnkbn, "'A");is a C statement, and for the C language the escape sequence is (\) not a single quote (‘). So, in your statement the single quote is seeing as a common character.

Author: Sandy
Last update: 2016-03-07 09:45


In Oracle, user can write the following statement to implement the conversion from date type to char type, how can I do that in DBMaker? select * from QMDVFCMDBS where sts_tkystr = to_char(sysdate,'yyyymmdd')

There also have conversion functions todate and datetostr in DBMaker which located in \DBMaker\x.x\shared\udf. It need user to create UDF first.

Alternatively, you can implement it by the statement:

select replace(cast(curdate() as char(10)),'-','') as new_char;

Suppose there is a table whose schema is:

create table SYSADM.B (

 STS_TKYSTR  CHAR(10) default null ,

 STS_TKYEND  CHAR(10) default null )

You can do the select as:

select * from b where STS_TKYSTR <= replace(cast(curdate() as char(10)),'-','') and STS_TKYEND >= replace(cast(curdate() as char(10)),'-','');

Author: Sandy
Last update: 2016-03-07 09:52


How to investigate the reason if face deadlock problem? (From DBMR1703, version: DBMaker 4.1.2, OS: Windows XP Embedded SP1)

To investigate the reason, you should monitor lock first, for example, as the following:

dmSQL> select USER_NAME, TABLE_NAME, SYSLOCK.LK_OBJECT_ID,

SYSLOCK.LK_GRAN, SYSLOCK.HOLD_LK_CONNECTION, SYSLOCK.LK_CURRENT_MODE

from SYSLOCK, SYSTEM.SYSTABLE, SYSUSER where HOLD_LK_CONNECTION = CONNECTION_ID and LK_OBJECT_ID = TABLE_OID and TABLE_OWNER != 'SYSTEM' and LK_GRAN != 'SYSTEM';

 

USER_NAME  TABLE_NAME  LK_OBJECT_ID     LK_GRAN   HOLD_LK_CONNECTION   LK_CURRENT_MODE

==========   ==========    ==============   ========   ====================    ==================

SYSADM                          D    1b00000000000600     TABLE                                       575                                      X

1 rows selected

Then you should select SYSUSER in every several seconds to get information about locks status on objects when dead lock occurred.

All the information that gets from the above will helpful for you to investigate the reason.

Author: Sandy
Last update: 2016-03-07 09:56


How to collect the Database system information? (Form as the above)

Generally, when users across problem, they need to analyze the database system information before the problem occurs and when it occurs. So user needs to monitor the system information to know the system status and manage the system better, then help to solve problem.

To monitor system information continually, timer function is needed. You can write a timer program or use the timer that provided by operating system. Linux platform provides the “crontab” command, Windows platform provided the “at” command or to set task manager by graphical interface.

The following script we give is about Linux platform example using “crontab” command and dmsqlc tool to monitor system periodically. User can use “at” command and dmsql32 tool to set executing similar script in windows platform.

  1. crontab to set every 5 min and Mon-Friday to run  checksys.bat$HOME/checksys.bat

crontab -e

*/5 * * * 1-5 $HOME/checksys.bat

2. checksys.bat (chmod +x checksys.bat)

cd $HOME

dmsqlc $HOME/script/ttt >> $HOME/checksys.out

3. ttt script

connect to dbsample4 sysadm;

select now();

set linewidth off;

select * from sysinfo;

select * from sysuser;

;You can add the SQL command to Monitor Lock

select now();

disconnect;

q;

 

Author: Sandy
Last update: 2016-03-07 10:01


How to insert data that contain single quotes into NCLOB column?(From DBMR1711)

The NCLOB data type is a variable length data type that can contain any Unicode character.When entering NCLOB data, it needs to enclose the Unicode character with single quotes and prefix the quotes with 'N'.

So if the NCLOB data you want to enter contains single quotes ('), it needs use the escape character.  In DBMaker, the escape character is single quotes ('). I.e. add another single quote before the single quotes in the data, then that will be ok.

For example:

dmSQL> def table tta;

create table SYSADM.TTA (

 C1  NCLOB default null )

 in DEFTABLESPACE  lock mode page  fillfactor 100 ;

Entering the 'a'b' to NCLOB column:

dmSQL> insert into tta values(N'a''b');

1 rows inserted

dmSQL> select * from tta;

C1

========================

610027006200

1 rows selected

dmSQL> select cast(c1 as char(40))from tta;

CAST(C1 AS CHAR(40))

========================

a'b

1 rows selected

Author: Sandy
Last update: 2016-03-07 10:07


What value will be returned if one of the string expressions contains a NULL value in the CONCAT function?

What value will be returned depends a setting which syntax is

     SET CONCAT NULL RETURN { NULL | STRING }

This option is used for string concatenation with null for the CONCAT built-in function or concatenate operator (||).

The default setting for this option is NULL. If this option is set to NULL, then any string concatenated with a null value will return null.

If the option is set to STRING, then any string concatenated with a null value will return the string, because the null value will be treated as an empty string.

For example:

dmSQL> select concat ('aaa', null);

                    CONCAT ('AAA', NULL)                              

===================================================================

NULL                            

1 rows selected

dmSQL> set concat null return string;

dmSQL> select concat ('aaa', null);

               CONCAT ('AAA', NULL)                              

===================================================================

aaa                                                                  

1 rows selected

Author: Sandy
Last update: 2016-03-07 10:11


What is the encryption method of UID/Password in DBMaker?(From DBMR1717)

The encryption technique used by DBMaker is a mix of DES and RSA.

Author: Sandy
Last update: 2016-03-07 10:14


Are the User ID and Password encrypted when connecting to database via network? Or they only be encrypted when setting DB_NetEc=1? Is any possibility of leakage UID and Password in network with setting DB_NetEc=0?

Yes. UID and Password are encrypted in the networking flow no matter whether set keyword DB_NetEc. Even if setting DB_NetEc=0, it is impossibility of leakage UID and Password in network.

Author: Sandy
Last update: 2016-03-07 10:16


What ADO functions does DBMaker support? (From DBMR1726)

There are three layers in ADO model, according to each model, the situation of the DBMaker supports are:

1. DBMaker supports Microsoft Activex Data Objects Library, but DBMaker does not support server side cursor in OLEDB Driver.

2. DBMaker does not support Microsoft Activex Data Objects (Multi-Dimensional) Library.

3. DBMaker does not fully support Microsoft ADO Extension for DDL and security.

Author: Sandy
Last update: 2016-03-07 10:18


Why it can connect to DBMaker 4.1 but cannot connect to 4.2/4.3 with the same connection string? (From DBMR1734)

It is because that the check rules which DBMaker used to check connection string between 4.1 and 4.2/4.3 are different.

For 4.1, DBMaker used simple check rule to check connection string, so if the connection string has some useless character, DBMaker 4.1 also can allow it to connect to database.

But for 4.2 series and 4.3.0, DBMaker adopted strict check rules for connection string, if there are any useless characters or unknown strings in connection string, DBMaker will return error. That's why 4.1 can pass and 4.2/4.3 cannot.

And after DBMaker 4.3.1 (include 4.3.1), we will change back the rule of checking connection string to simple.

Author: Sandy
Last update: 2016-03-07 10:19


What the correct connection string should be? (From DBMR1734)

For connection database, DBMaker supports two methods: Register DSN and do not Register DSN.

If you have registered DSN, The correct connection string that can connect to the DBMaker Database successfully should be as:

strConn = "DSN=data_source_name;UID=user_name;PWD=password"

If you did not register DSN, please follow the following connection string format when use ADO:

strConn = "Driver=DBMaker 4.1 Driver;

DATABASE=Dbsample4;

SvAdr=192.168.0.164;

PtNum=8888;

UID=sysadm;

Pwd=;

ATCMT=1;"

And please pay attention to the following items:

  1. The order of the keywords should not be changed.
  2. To different database, it should modify the values of the following keywords:

DATABASE=;

SvAdr=;

PtNum=;

3. For different versions of DBMaker and DBMaker, it should modify the value of the Driver keyword to keep consistent with the software.

For example:

If the connected database is DBMaker 4.1, set Driver=DBMaker 4.1 Driver;

If the connected database is DBMaker 4.2, set Driver=DBMaker 4.2 Driver;

If the connected database is DBMaker 4.1, set Driver= DBMaker 4.1 Driver;

If the connected database is DBMaker 4.2, set Driver= DBMaker 4.2 Driver;

 

Author: Sandy
Last update: 2016-03-07 10:26


If I want to reduce disk I/O,what should be done with DBMaker? (From DBMR1742)

DBMaker 4.3 supports two internal configuration keywords (DB_DSYNC, and DB_JSYCN), they can turn off DBMaker flush function and let the OS to control IO. But it's dangerous, because If the system crashed by abnormal operation, it is possible to cause Database data inconsistent. So generally speaking, we do not suggest do that.

If surely want to reduce the database’s I/O, try doing the following items:

  • Ÿ    set DB_JSYNC = 0 (turn off journal file flush function, before start db, set it in dmconfig.ini)
  • Ÿ    set DB_DSYNC = 0 (turn off database data file flush function, before start db, set it in dmconfig.ini)
  • Ÿ    set DB_STSVR = 0 (turn off statistics server)
  • Ÿ    set DB_BKSVR = 0 (turn off back up server)
  • Ÿ    set DB_IOSVR = 0 (turn off IO daemon server)
  • Ÿ    don't use DISTINCT,GROUP BY, ORDER BY at query, join tables (if join result is huge), this is for avoid sorting by DBMaker (creating a temp file)
  • Ÿ    don't create System File Object (avoid file IO)
  • Ÿ    don't use Text Index , rebuild index (avoid index file IO)
  • Ÿ    turn off tablespace Auto-extend fuction.

Author: Sandy
Last update: 2016-03-07 10:46


When I try to connect to the DB server with the dmSQL tool, why it returned the below error and failed? (From DBMR1741) ERROR (1119): [DBMaker] OS shared memory setting is too small, please increase OS shared memory maximum size. [dbstart. 816],0,0,0

It is not the memory‘s business, Please set keyword DB_SVADR for this database. For example:

 

DB_SVADR=192.168.0.170;

That will be ok.

 

Author: Sandy
Last update: 2016-03-07 10:48


2007

Why dmserver failed to run on the machine that installed antivirus software? (From DBMR1737 & 1754)

It may be the antivirus software’s business. Some antivirus software, such as NOD32, can prevent DBMaker accessing db files, then cause the dmserver fail to run. For NOD32, you can add DB installation directory and directories where the database files reside into white list of AMON in NOD32, white list is list of files, folders which be excluded from scanning, or you can disable the file scanning function in NOD32 to salve the problem.

Author: Sandy
Last update: 2016-03-07 10:56


Why the previous DBMaker version can not start the DB after the DB was started by higher DBMaker version? Then how to salve? (From DBMR1747)

Once you started the DB with the higher version, some file version or sys tables were modified, and the higher database can not be downgrade to lower DB or earlier versions. So it will not be started with the previous version any longer.

But you can unload the DB first then reload it into the previous DBMaker version using dmSQL.

Author: Sandy
Last update: 2016-03-07 10:57


Why the memory occupation reduced when minimized the dmserver window in Windows Platform? (From 1748)

It should be windows’ behavior. Not only the dmserver can show the phenomenon, other applications such as Outlook or IE can also show the phenomenon. This behavior will not reproduce if you minimize dmserver to system tray or start the DB as a service.

Author: Sandy
Last update: 2016-03-07 10:59


What are the general steps about installing DBMaker Client and DSN on Windows platform manually? (From DBMR1783; Version: DBMaker 4.16)

Generally, there are 4 steps to install DBMaker client and DSN; they are as following (take DBMaker 4.16 for example)

Step1: Copy Filesinsure the following files must be exist in the correct directory.

Directory

files

Windows home directory

(Such as C:\Windows\, C:\Winnt)

dmconfig.ini

dmerr41.tab

DMSQL.INI

Windows system directory

(Such as C:\Windows\system32\, C:\Winnt\system32)

dmapi41.dll

DMSQL.INI

(DBMaker Home directory)

\4.1\

COPYRIGHT

EVALUATION

grammar.sql

README.TXT

(DBMaker Home directory)

\4.1\bin\

dmdttut.dll

dmjdbaut.dll

dmjdbc20.jar

dmjdbc20xa.jar

dmjdbc30.jar

dmjsvrut.dll

dmppc32.exe

dmppcc.exe

dmset.dll

dmsql32.exe

dmxtm.dll

dmxtm.jar

dmxtt.dll

dmxtt.jar

expat.dll

icudt22l.dll

icuin22.dll

icuuc22.dll

jcfgditor.ico

jdatatran.ico

jdbatool.ico

jh.jar

jhelp.jar

jmonitor.ico

jsvrmanager.ico

jtools.jar

startup.jar

xerces-c_2_1_0.dll

xtm.ico

xtm.jar

xtt.ico

xtt.jar

(DBMaker Home directory)

\4.1\include\

dmxtm.h

dmxtt.h

eqmacro.h

eqspincl.h

esqlda.h

esqlincl.h

esqlprot.h

esqltype.h

jni.h

jni_md.h

libudf.h

odbc.h

sql.h

sqlca.h

sqlext.h

sqlopt.h

sqltypes.h

sqlucode.h

sqlunix.h

XMLTransfer.hpp

XTMTransfer.hpp

(DBMaker Home directory)

\4.1\jre\

All files

(DBMaker Home directory)

\4.1\lib\

dmapi41.lib

dmxtm.lib

dmxtt.lib

(DBMaker Home directory)

\4.1\ shared\errtab\

dmisql.tab

dmJdbc.properties

dmjtool.tab

(DBMaker Home directory)

\4.1\ shared\codepage\

dmB5LToU.map

dmB5UToL.map

dmCyrillicLToU.map

dmCyrillicUToL.map

dmGBKLToU.map

dmGBKUToL.map

dmGreekLToU.map

dmGreekUToL.map

dmJISLToU.map

dmJISUToL.map

dmLatin1LToU.map

dmLatin1UToL.map

dmLatin2LToU.map

dmLatin2UToL.map

Some tools can directly be used by opening the exe file, such as dmppcc (dmppc32.exe) and dmSQL(dmsql32.exe). But for other JAVA Tools, creating shortcut is used to open DBMaker JAVA Tool.

Create shortcut of JDBATool:

Target: C:\DBMaker\4.1\jre\bin\javaw.exe -classpath startup.jar;dmjdbc20.jar;jhelp.jar;jtools.jar jdba

Begin Location: C:\DBMaker\4.1\bin

ICO: %SystemDrive%\DBMaker\4.1\bin\jdbatool.ico

Step2: Configure dmconfig.ini file

     [DBSAMPLE4]

     DB_PTNUM = 2453

     DB_SVADR = 192.16.0.121   ; address of the server machine

     DB_USRID = SYSADM

Step3: Registry ODBC Driver

[HKEY_LOCAL_MACHINE\SOFTWARE\DBMaker]

[HKEY_LOCAL_MACHINE\SOFTWARE\DBMaker\4.1]

"HOME"="C:\\DBMaker\\4.1"

"COMPILER"=""

"INSTALLTYPE"="Client"

"CURRENTVERSION"="4.1.6"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DBMaker 4.1 Driver]

"Driver"="C:\\WINDOWS\\system32\\DMAPI41.DLL"

"Setup"="C:\\DBMaker\\4.1\\bin\\DMSET.DLL"

"APILevel"="1"

"ConnectFunctions"="YYN"

"CPTimeout"="60"

"DriverODBCVer"="03.00"

"FileUsage"="0"

"SQLLevel"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]

"DBMaker 4.1 Driver"="Installed"    

Step4: Registry DSN

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DBSAMPLE4]

"Driver"="C:\\WINDOWS\\system32\\DMAPI41.DLL"

"Database"="DBSAMPLE4"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"DBSAMPLE4"="DBMaker 4.1 Driver"

Author: Sandy
Last update: 2016-03-07 11:05


What are the general steps about installing DBMaker Driver and DSN on Windows platform manually? (From DBMR1783; Version: DBMaker 4.16)

Generally, there are 4 steps to install DBMaker Driver and DSN; they are as following (take DBMaker 4.16 for example)

Step1Copy Files, insure the following files must be exist in the correct directory.

Directory

files

Windows home directory

(Such as C:\Windows\, C:\Winnt)

dmconfig.ini

dmerr41.tab

Windows system directory

(Such as C:\Windows\system32\, C:\Winnt\system32)

dmapi41.dll

dmjdbc41.dll

(DBMaker Home directory)

\4.1\

COPYRIGHT

EVALUATION

README.TXT

(DBMaker Home directory)

\4.1\bin\

dmjdbc20.jar

dmjdbc20xa.jar

dmjdbc30.jar

dmset.dll

(DBMaker Home directory)

\4.1\ shared\errtab\

 

dmJdbc.properties

(DBMaker Home directory)

\4.1\ shared\codepage\

dmB5LToU.map

dmB5UToL.map

dmCyrillicLToU.map

dmCyrillicUToL.map

dmGBKLToU.map

dmGBKUToL.map

dmGreekLToU.map

dmGreekUToL.map

dmJISLToU.map

dmJISUToL.map

dmLatin1LToU.map

dmLatin1UToL.map

dmLatin2LToU.map

dmLatin2UToL.map

Step2: Configure dmconfig.ini file

     [DBSAMPLE4]

     DB_PTNUM = 2453

     DB_SVADR = 192.16.0.121   ; address of the server machine

     DB_USRID = SYSADM

Step3:Registry ODBC Driver

[HKEY_LOCAL_MACHINE\SOFTWARE\DBMaker]

[HKEY_LOCAL_MACHINE\SOFTWARE\DBMaker\4.1]

"HOME"="C:\\DBMaker\\4.1"

"COMPILER"=""

"INSTALLTYPE"="Driver"

"CURRENTVERSION"="4.1.6"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DBMaker 4.1 Driver]

"Driver"="C:\\WINDOWS\\system32\\DMAPI41.DLL"

"Setup"="C:\\DBMaker\\4.1\\bin\\DMSET.DLL"

"APILevel"="1"

"ConnectFunctions"="YYN"

"CPTimeout"="60"

"DriverODBCVer"="03.00"

"FileUsage"="0"

"SQLLevel"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]

"DBMaker 4.1 Driver"="Installed"    

Steps 4: Registry DSN

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DBSAMPLE4]

"Driver"="C:\\WINDOWS\\system32\\DMAPI41.DLL"

"Database"="DBSAMPLE4"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"DBSAMPLE4"="DBMaker 4.1 Driver"

Author: Sandy
Last update: 2016-03-07 11:08


What is the process that ASP accesses DBMaker? (From DBMR1797 ;)

The process that ASP accesses DBMaker is as following (Connection string = “DSN=ISDB;UID=SYSADM;PWD=”):

 IIS -> ADO -> MSDASQL ->MS ODBC Driver Manager -> DBMaker Driver -> DBMaker Server.

Author: Sandy
Last update: 2016-03-07 11:11


Sometimes, monitoring the database sessions with JDBATool finds that the connection of the ASP page is not released when the connection.close method is executed. Then it remains up for about 40 seconds, why? (From DBMR1797 ;)

It is not DBMaker Server’s behavior; the connection remains because the client does not release connection actually. The reason may be IIS, ADO or ODBC Driver has connection pooling.

DBMaker server is able to break a connection forcibly, but it is not able to inform client to release connection. If user wants to control connection timeout in ASP, please investigate IIS, ADO and ODBC connection pooling. BTW, if some client program don’t release opened connection actively, DBMaker can break idle connections via setting keywords DB_DtClt and DB_ITimO in dmconfig.ini file.

We guess: When performing open connection operation in ASP code, IIS can load ADO component and create a connection to DBMaker via ADO. But performing close connection operation even if destroy connection object (set conn = nothing) in ASP code, IIS does not release this connection from DBMaker immediately. After this, if we open any page which connect to DBMaker, IIS uses existence connection but not create a new connection. If no new request during a long period (such as 40 seconds), IIS will release this connection.

Author: Sandy
Last update: 2016-03-07 11:12


How is the ALTER TABLE statement treated by DBMaker internally? (From DBMR1740)

1. Before DBMaker 4.1x (include 4.1x) series, 'ALTER TABLE' statement should copy another internal table (select into), so it occupies double disk space and writes into journal (please take care journal full problem).

 2. After DBMaker 4.2x (include 4.2x) series, only add ONE column in the tail of table and default value is null can go smart flow (just modify table schema, table data doesn't any reference for DB) to ALTER TABLE. Besides this case, all of 'ALTER TABLE' SQL commands also need to occupy double disk space and write journal file (also take care journal full issue).

 3. The method flow is: Create a new internal table -> select from modified source table into new internal table -> drop modified table -> rename new internal table -> mark internal table to public.

 4. SELECT...INTO will write journal file also, it will take huge journal file space. Before execute 'ALTER TABLE' with a huge data table, please check journal file is enough.

Author: Sandy
Last update: 2016-03-07 11:13


What is the detailed information about the dmconfig.ini keyword DCI_UDCUR?

The DCI_UDCUR keyword has been removed from DBMaker since 4.2 series (after 01/06/2006). In early version, the keyword was used to increase speed in the single process. And the default update current is ON. When user needs to update a record, and the record was read just now, it will judge whether the index of update is same with the one that just read now and whether key data of the two indexes are same. If they are same, using "update where OID = xxx".

But in multi-process, this setting has some risk; because when reading key, fetched data may be modified by other connections, so it will be result in data inconsistent.

In addition, with the new DBMaker version, user must use REWRITE/DELETE with PK or unique key to prevent updating/deleting many records.

Author: Sandy
Last update: 2016-03-07 11:15


Is it possible to control the ON/OFF of ODBC log by SQL? (From DBMR 1805)

At present, user can only control the ON/OFF of ODBC log by setting dmconfig.ini and restarting DB. Restarting DB makes keyword settings efficacious.

Author: Sandy
Last update: 2016-03-07 11:21


Can the Shift-JIS and EUC-JP data be stored in the same DB or same table? (From DBMR 1805)

At present, it is impossible in DBMaker 4.x and it may be come true in DBMaker 5.0. And now, the Shift-JIS and EUC-JP data can only be stored in two different DB respectively.

Author: Sandy
Last update: 2016-03-07 11:24


We can choose the character set when installing DBMaker, does it means we also choose the message file character set? (From DBMR 1805)

There are two steps to choose language and character set during installing DBMaker.

The character set only decides data stored format, and is not related with DBMaker message file.

At very beginning of installing DBMaker, you can select language code; it affects that DBMaker use which language to display and return DBMaker message in GUI tools and other tools.

The secondary choice of language is to decide use which character set to save data in DBMaker by default.

Author: Sandy
Last update: 2016-03-07 11:25


If wants to change the character set, should it need to reinstall DBMaker and recreate DB? And is there encoding method like NLS_LANG of Oracle, so that user can not have to recreate DB? (From DBMR 1805)

If wants to change the character set, user only needs to recreate DB with new DB_LCODE.

NLS_LANGE in Oracle is a parameter which effects client side character set. Setting NLS_LANGE can change client side character set.

DBMaker database character set is decided by DB_LCODE keyword. Client side character set is the default character set of the client side OS. DBMaker has no setting for client side character set, only supports DB_LCODE for Server side.

Author: Sandy
Last update: 2016-03-07 11:26


In DBMaker Stored procedure, If use the temporary table to save the result set, how to deal the temporary table, (when to drop it)? (From DBMR 1800)

The temporary table just exists in current session; DBMaker will automatically drop the temporary table when exiting current session. So if use the temporary table to save the result set in stored procedure,  user should not drop the temporary table directly after getting result.

If execute the stored procedure more than one time in current session, then the second time it will show error: the table has existed. So it needs to add “drop table table_name” statement at the beginning of the sp to avoid the case.

And After “$ begin code section;” statement, please add the following two statements

$ WHENEVER SQLERROR CONTINUE;

$ drop table temporanea;

Author: Sandy
Last update: 2016-03-07 11:27


What the possible reasons that can cause the Client disconnect?

The possible reasons may be:

1. Idle time out.

2. Server restarts.

3. Network issue.

4. Bug; if it is bug, the error should be reproduced each time.

5. Socket error: like socket cannot write. Just as DBMR1806.

6. User disconnects.

7. Kill connection by DBA or SYSADM.

Author: Sandy
Last update: 2016-03-08 04:47


Can DBMaker insert text file of UTF16 format into NCLOB column?

Before inserting it into NCLOB, DBMaker converts the text file to Unicode first, so if the file is saved as Unicode, it should not be converted, it can be inserted directly. So from 4.2(contain 4.2) issue, DBMaker support the command”set bfiletype Unicode/ASCII” (the default set is ASCII). With this command the Unicode text file can be inserted into NCLOB column directly.

For example:

First, converts the README.TXT to UTF16 coding, and then saves it in C:\DBMaker\4.3 directory.

 

dmSQL> create table UTF16(fileContent nclob, pk serial);

dmSQL> set bfiletype unicode;

dmSQL> insert into UTF16 values(?);

dmSQL/Val> &'C:\DBMaker\4.3\README.TXT';

1 rows inserted

dmSQL/Val> end;

dmSQL> set bfiletype ASCII;  // default setting

 

Please pay attention, before the version 4.2; DBMaker did not support the command”set bfiletype Unicode/ASCII”. So user cannot directly insert text file of UTF16 format. One method to solve is converting Unicode file to local code file first, then insert. The Second is using program, open file, and then putData with Unicode data type.

Author: Sandy
Last update: 2016-03-08 04:50


How to disconnect the useless sessions? (From MR1809)

To disconnect the connection automatically, DBMaker supports two keywords DB_ITimO and DB_DtClt.

The keyword DB_ITimO is used in Server side. DB_ITimO specifies the idle timeout interval, According to this value, DBMaker automatically disconnects connections those have no database operations with a higher value than the specified timeout interval. Before setting DB_DtClt, make sure the DB_ITimO is turned on (the default setting is disabled).

The keyword DB_DtClt is used in Client side. So each client can set DB_DtClt value for itself, and each client can have different detect times. DB_DtClt is only used to detect connection, it can not disconnect connection. For each client, server will use the maximum[1] value between DB_ITimO and DB_DtClt to disconnect connection.

Now, DBMaker can not identify the useless connections and the connections those have no database operations for long time (Idle). So the only method to disconnect the useless connections automatically is setting the DB_ITIimO to a larger value. For the less DB_ITimO value will disconnect some idles too.



[1] After DBMaker 5.0, the action will change; it will use the minimum value between DB_ITimO and DB_DtClt to disconnect the connection.

Author: Sandy
Last update: 2016-03-08 04:55


Why does the error 8349 occur? But the length of the data does not over the 1024 byte, although the index including a Varchar (2000) type column? And how to avoid this occur? (From DBMR1811)

(1). First, when creating an index which including varchar type column, DBMaker does not check the length of this index.

For example:

dmSQL> create table dbmr1811(id  SERIAL(1),application_name  VARCHAR(2000) default null );

dmSQL> create index idx_dbmr1811 on dbmr1811 (application_name);

The above idx_dbmr1811 includes a varchar(2000) column whose length is over 1024, but you can create the index successfully, Because DBMaker will not check the length until using the index.

(2). When using the above index, DBMaker will check length of part indices depend on different execution plan. When one execution plan uses nested join, it does not know the length of data, so it uses the definition length (2000 Bytes in this case) of varchar type as data length to allocate buffer. When checking length, if finding the length of checked index over 1024 Bytes, the error 8394 will occur.

For example:

dmSQL> create table c1(a1  VARCHAR(2000) default null );

dmSQL> create  index idx_c1 on c1 ( a1 );

dmSQL> create table d1(a1  VARCHAR(2000) default null );

dmSQL> create  index idx_d1 on d1 ( a1 );

dmSQL> select * from c1 JOIN d1 on c1.a1 = d1.a1 where 1=1;

ERROR (8349): [DBMaker] total length of index key exceeds maximum length (1024 Bytes)

(3).There are two methods to avoid it.

One is executing the “update statistics”. After executing "update statistics", execution plan uses merge join and the length of varchar is actual length but not definition length.

The other is using forced Merge join between tables. DBMaker 4.3 provides force optimizer syntax; you can use “MERGE JOIN…” syntax to let query run, then it use the MERGE JOIN but not the Nested JOIN.

For example:

dmSQL> select * from c1 MERGE JOIN  d1 on c1.a1 = d1.a1 where 1=1;

(4).Please pay attention, although successfully creating this type index (include varchar type column), data for inserting can not over 1024 bytes.

(5).You’d better don’t create ordinary index on long column, this type index will occupy many index pages and may not be used when query. So we suggest user creating text index on long column.

Author: Sandy
Last update: 2016-03-08 05:02


What is the meaning of Low Value and High Value for column statistics? (From DBMR1813)

You can see column statistics in JDBA Tool.

For example:

dmSQL> create table test(a serial);

dmSQL> insert into test values();

1 rows inserted

dmSQL> insert into test values();

1 rows inserted

dmSQL> insert into test values();

1 rows inserted

dmSQL> insert into test values();

1 rows inserted

dmSQL> select * from test;

     A     

===========

          1

          2

          3

          4

4 rows selected

For the sample, in JDBA, you can see the following statistics data for columns:

Column

#of Distinct values

Average Length

Low Value

High Value

A

4

4

2

3

 

So they are statistics values for column,

Low-value (loVal) means the second minimum value for a column;

High-value (hiVal) means the second maximum value for a column;

So in this case, Low Value is 2 and High Value is 3.

The maximum and minimum value is filtered by Statistics, so low-value and high-value will use the second maximum and minimum value.

Author: Sandy
Last update: 2016-03-08 05:05


How to check the status after starting DB with NT Service,and how to do when the service is on but the connect fail sometimes? (From DBMR1832)

After starting DB with NT service, the DB is in the middle of starting with creating Journal, at present, only journal file is increasing and it is not easy to judge DB's status from DB side.

User can set a wait time before "connect DB" or write a program to connect DB, if connection fails, retry, until connect to DB successfully.

Author: Sandy
Last update: 2016-03-08 07:44


Is there some method to make the serial number be reset automatically when it over the maximum value? (From DBMR1836)

By Stored Procedure and trigger can achieve that.

For example: suppose there is a table named t1: t1 (c1 serial)

[Stored Procedure]

$ create procedure sp1(char(32) tbname, int ns1); { $ begin declare section;

  char sqlstring[100];

$ end declare section;

 

$ begin code section;

 

if (ns1 >= 2147483646)

   {

   sprintf(sqlstring, "alter table %s set serial 0", tbname);

   $ execute immediate from :sqlstring;

   }

 

$ end code section;

 

}

 

[SQL command]

create procedure from 'sp1.ec';

create trigger tr1 after insert on t1 for each row (call sp1('t1', new.c1));

Author: Sandy
Last update: 2016-03-08 07:47


How to make effective use of memory, does it need changing some settings? (From DBMR 1839)

There are many factors effect performance, such as Hardware, OS, Database system, Application system.

For database factors, there are also many factors. Performance is decided by these factors. Memory Allocation is only one of them.

To achieve the best performance, please consider all these factors.

It is important to ensure there is enough physical memory to prevent the operating system from swapping the DCCA to disk too often or it will seriously degrade the performance.

Please refer to the following aspects to change setting:

  1. Page buffer size --- should depend on hit ratio to adjust.
  2. Journal file size and journal buffer --- if the system has long transactions, need to set big a little.
  3. SCA--- the more DB_LetPT and DB_LetRP setting, the more SCA. Because there are more lock records in SCA.
  4. Mainly adjust page buffer size, but not SCA.
  5. In addition, need to leave some free memory.

Performance Tuning should combine run situation of the real system. After the first setting, user should continually monitor the Database, and then turn.

Author: Sandy
Last update: 2016-03-08 07:48


What kind of action use memory space other than DCCA? (From DBMR 1839)

Each connection will be allocated one private memory not in DCCA but on DB Server side to save private data, such as sort data.  

ITT is local memory when needing to memory allocation, Operation System allows how much, DBMaker get how much. If the sorting data over ITT, DBMaker will create external file.

Memory table is local memory also, if over the allocated size, DBMaker will return 'out of memory’.

Author: Sandy
Last update: 2016-03-08 07:49


How to avoid garbage character for following situation when use the character set UTF-8 in Web system? (From DBMR 1847) 1. Web AP insert/select UTF-8 data. 2. Web AP insert data and update the data by Ms Access and so on. 3. MS Access or dmSQL insert data into DB and Web AP select the data.

1. Web AP operating UTF-8 data is no problem, DBMaker only support U16LE.

2. If storing data as nclob/nvarchar/nchar, customer can use MS Access to access data by ODBC Driver Manager (MS Driver Manager internal is U16LE).

3. User can create column type as nclob/nvarchar/nchar to store Unicode data, but should translate from U8 <=> U16LE by themselves for insert/fetch.

4. before inserting data by dmSQL it should convert data to Unicode first, then use 'xxxx'u to insert data into database. It means They can not directly insert UTF-8 data by dmSQL, before inserting, should convert UTF-8 to UTF-16, and then insert UTF-16 data.

There are two methods:

A).DBMaker will provide customer UDF - U8toU16/U16toU8 to help user to translate data, but they should declare column type as nclob/nchar/nvarchar.

select u8tou16(c1) from t1;

The data will be correct and no garbage character; dmSQL cannot see the readable data.

B). Like SQL Server, store UTF8 data into binary column type, but cannot use db search function (like...etc), this is SQL Server solution.

Using UDF should modify their program, but use binary to store does not modify.

Author: Sandy
Last update: 2016-03-08 07:52


Does DBMaker support the functions TO_DATE/TO_CHAR/TO_TIMESTAMP?

TO_DATA: DBMaker supports this UDF, but users should add it by themselves, checking DBMaker installation\4.3\shared\udf\to_date.sql or using cast to achieve.

TO_Char: using cast to achieve.

TO_Timestamp: using cast to achieve.

Author: Sandy
Last update: 2016-03-08 07:53


How to use the NCHAR (unicode) data type for host variables in ESQL/C?

Please check esqltype.h has defined NCHAR like below:

typedef char    nchar;             /* for unicode #004 */ typedef varchar nvarchar;          /* for unicode #004 */ typedef longvarchar nclob;         /* for unicode #004 */ 

If esqltype.h has this define, user can use nchar in ESLQ/C, but user should put correct unicode (transfer local code to unicode by themselves) data into nchar buffer.

For example:

create table nt1 (c1 int, c2 nchar(10));

 /****************************************************************************
 * test nchar: test nchar

 * NOTE: user must make sure their input unicode data is correct, we do not provide unicode <-> local utility function

 **********************************************************
test_nchar()
{
   EXEC SQL begin declare section;
   nchar tc2[22];
   nchar hvstring[300];
   char buf[300];
   int tc1, ind1, ind2;
   EXEC SQL end declare section;
   int i;

 

   printf("\ntest nchar: -----\n");

 

   $ delete from nt1;
     
   // this is a local string
   sprintf(buf, "insert into nt1 values (1, '1234567890')");
   // The following is a lazy way to convert ascii string
   for (i = 0; i < strlen(buf); i++)
      {
      hvstring[i*2] = buf[i];
      hvstring[i*2+1] = 0;
      }
   // Add NULL terminate 0x0000 for unicode string
   hvstring[i*2] = hvstring[i*2+1] = 0;

 

   // execute the unicode sql command
   $ execute immediate from :hvstring;
   chkErr();
  
   // select the result to a unicode string buffer
   $ select * from nt1 into :tc1 :ind1, :tc2 :ind2;
   chkErr();
  
   printf("tc1=%d, ind2=%d, tc2=", tc1, ind2);
  
   // print out the hex for the unicode string
   for (i = 0; i < ind2; i++)
      printf("%02x", (unsigned char)tc2[i]);
     
   $ delete from nt1;
   chkErr();

 

   // insert the unicode string data to table  
   $ insert into nt1 values (:tc1 :ind1, :tc2 :ind2);
   chkErr();
  
   printf("\n");

 

   $ commit;
   chkErr();
}

 

Author: Sandy
Last update: 2016-03-08 08:00


Why the JTools can not be started up in DBMaker Client side? (From DBMR1842; Version: DBMaker 4.1.2)

There may be two reasons, and the most possible cause is privileges control.

  1. Maybe the Antivirus soft prevents access. Please check all Antivirus soft to confirm.
  2. The current user may have not ACCESS privilege: Check all related directory and files privilege, such as in directories and subdirectories in DBMaker installation directory to confirm whether the current user has “read and write” privilege. If the current user has not these privileges, the problem will occur.

For DBMaker 4.1.2, most of files and sub folders under JRE folder do not have "read and write" for "Everyone". Changing them to "read and write" for "Everyone", the problem is solved.

The problem exists on DBMaker 4.1.2, but not exists after 4.1.2 version.

Two versions used different install tool and different install tool(install shield and inno setup)has different action which cause the problem. So user should grant privilege first, or install high version DBMaker client (after 4.1.2).

Author: Sandy
Last update: 2016-03-08 08:01


Does the order of the lib files matter when you build the wrun32.dll?

 In AcuGT5.x, if adding the DCI libraries in the following order: dmapi50.lib dmacu52.lib and dmdcic.lib (make sure the dmapi50.lib is the first one of those 3 lib files), run the COBOL program without any error. But in AcuGT6.x, the same order of lib files may result in errors. However, if building wrun32.dll with the lib files in the following order: dmacu52.lib, dmdcic.lib and dmapi50.lib (dmapi50.lib is the last one), you can run ACU COBOL program without any errors.

It is because AcuGT 5.x and 6.x are different version; 6.x may have some specialties on checking order of link libraries. DBMaker DCI has not done any changing on this part.

The problem should be related with different AcuGT versions.

In addition, the two versions use different methods to build wrun32.dll: AcuGT5.x uses the wrun32.mak file, and AcuGT6.x uses wrun32 VC++ 6.0 project (wrun32.dsw).

Author: Sandy
Last update: 2016-03-08 08:03


How to backup/recover DB with zip/unzip function by command? (From DBMR 1846)

DBMaker only compress the full backup files, incremental backup will not be compressed because the incremental backup file is smaller and compress it may cause some overhead when restore database.

At present, DBMaker does not support command function to restore compressed backup files. Zip backup file feature should depend on backup server or JServer Manager Tool when DB_BKZIP is turned on.

Author: Sandy
Last update: 2016-03-08 08:05


In DBMaker 4.0, where is the Audit log file output to? Please tell me both cases: 1.start db by service. 2. Start db by dmserver and in which version can set AUDIT LOG directory?

Audit log file will be located in one of the following directories:

1. OS system directory (such as C:\WINNT\system32).

2. DBMaker home directory\bin directory or working directory.

3. DB_AUDIR: 4.3 supports setting AUDIT LOG directory. If not setting DB_AUDIR, AUDIT LOG is under DB_DbDir.

In 4.0, user cannot appoint AUDIT LOG directory, but can search the DBNAME.LOG file.

 

OS

Starting DB by NT Service

Starting DB by dmserver

Note

windows 2003 server

C:\WINDOWS\System32

DBMaker\4.0\bin

If windows 2003 server is 64bit,

start db by service:   C:\WINDOWS\SysWOW64.                    

if windows 2003 server is 32bit,

start db by service:C:\WINDOWS\System32.   

windows 2000 server

C:\WINNT\system32

DBMaker\4.0\bin

N/A

Author: Sandy
Last update: 2016-03-08 08:07


While there are two Unicode records in a table, one is the Simplified Chinese and one is Japanese. Why it returns all but not the correct one record which according to the search condition when executing a search? (Version: 4.2)

When setting the DB_LCODE to value of 1 in DBMaker 4.2 version, the SIGNATURE TEXT INDEX will return all, but the IVF TEXT INDEX will return the correct one. At present, the SIGNATURE TEXT INDEX does not support Unicode and the IVF TEXT INDEX does. So we suggest dropping the SIGNATRUE TEXT INDEX and building the IVF TEXT INDEX to solve this problem.

Please refer to the following example.

dmSQL>create table test_srh (ID serial,Name char(30),TextBlock NCLOB);

dmSQL>create  text index IDXTEXTBLOCK on test_srh (TextBlock);

dmSQL>insert into test_srh (TextBlock) values ('a080505b'u);        <-- 塞入资料为  肠子

dmSQL>insert into test_srh (TextBlock) values ('e130fc30eb30'u);     <-- 塞入资料为  メール

dmSQL>rebuild text index IDXTEXTBLOCK for test_srh;                 <-- 重建 text index

dmSQL>select * from test_srh where TextBlock match 'a080'u;          <-- 查询 肠

 

    ID                   NAME                 TEXTBLOCK

=========== ============================== ================

          1              NULL              a080505b

          2              NULL              e130fc30eb30

 

 

dmSQL> drop text index idxtextblock from test_srh;

 

dmSQL> create ivf text index idxtextblock on test_srh (TextBlock);

 

dmSQL>  rebuild text index IDXTEXTBLOCK for test_srh;

 

dmSQL>  select * from test_srh where TextBlock match 'a080'u;

 

    ID                   NAME                 TEXTBLOCK

=========== ============================== ================

          1              NULL              a080505b

Author: Sandy
Last update: 2016-03-08 08:17


2008

Why does DBMaker not use NAS

The reasons that DBMaker does not use NAS are:

1.Simply, we cannot warrant and support this way.
2.Network makes performance worse. 
3.Network failure can cause data inconsistency.
4.It can not make sure journal buffer can flush successfully. 
5.If user put data and blob on net Network Storage, when journal file is on local, DBMaker should run OK, but we can not make sure the DB can be restored when some problem occurred. So, for safety, we do not suggest to use it.

Author: ququ
Last update: 2016-01-12 03:31


Why does some query become slower after some days, and updating statistics does not speed them up. The only way to have these queries fast as before is to download tables, drop tables and re-load tables. Why does this problem occur?(From 1851, version: DBMaker4.3)

This may be a special case about optimizer; DBMaker optimizer supposes the data distribution is Uniform in table. The optimizer can not find out merge join (the best execution plan) to QEP if the customer data distribution is skew, then it will adopt nested join to execute.
Because the data is skew, so after customer executing update statistics table_name simple=100 to get real statistic value, DBMaker optimizer also using nested join to execute.
Even create another index; the optimizer will take Primary Key as execution index.
But after unload and load, the default optimizer will use the default statistic value to make execution plan and it supposes the data distribution is uniform, so it will adopt the best execution plan.
This is not a bug; DBMaker has improved the optimizer formula to support all kinds of distributions of the data and to make the best execution plan.

Author: ququ
Last update: 2016-01-12 03:33


Which Linux platform does DBMaster 5.0 support

After 5.0, DBMaster will change release issue for Linux platform from Glibc2.1 upgrade to Glibc2.3.

Please pay attention:

1. Glibc2.1 Linux environment can not run in Glibc2.3 environment.

2. Glibc2.1 will move to Level 2 supported by customer request, the default (level 1) Linux is Glibc2.3 building.

Author: ququ
Last update: 2016-01-12 03:47


Can you explain about the contents and role of SDB file? (From DBMR 1854)

SDB files store the system
catalog and user table's definitions and index. For example: SYSTABLE,
SYSCOLUMN and user table's definition.

Author: ququ
Last update: 2016-01-12 03:49


What cause the DB file and SDB corruption? (From DBMR 1854)

When this happens, it should not be DB engine's problem. In most case, DB file damage is caused by hardware and environments, for example: thunderbolt, suddenly power off, net device problem, OS fail, virus...

We never heard DBMaster SDB file corruption from users in the past. And if DBMaster DB file were damaged, user can send their DB file to us, we will check the problem on the data page or record and try to fix it.

Because hard disk may crash or corrupt, it's always important to backup their database. It’s also very important to ask user to always terminate their database properly. It's also better not to start db with new journal unless necessary. If user have abort transaction when terminate database, it's also better to start the database twice to ensure the database redo/undo operation has been done completely.

Author: ququ
Last update: 2016-01-12 03:50


Why is the .msg file empty when use the Stored Procedure and occur the compile error

Generally speaking, if the .msg file is empty, it means user does not have a C compiler installed. Please install C compiler, such as VC6 or VC7 or .NET C++.

At present, DBMaster 5.0 only supports VC6 and .NET 2003 two kind compilers.

Author: ququ
Last update: 2016-01-12 03:51


Why does DBMaker duplicate the journal file without having an incremental backup? (DBMR 1875)

This is a normal action which can avoid journal full problem. Although without
having an incremental backup, Backup Server (setting DB_BkSvr = 1) will
continue to perform incremental backup when journal files full.

Author: Sandy
Last update: 2016-03-08 09:07


There are the following statements: On the machine the database was last started from before it terminated abnormally, use the UNIX ipcrm utility to remove the allocated shared memory from the system. And which key has to be removed from the shared memory? It's always the same? (From DBMR 1880)

 ipcs:check the shared memory allocation on a system
ipcrm:manually deallocate shared memory on a system

When removing the shared memory, please use shmid (SharedMemory ID). After some operation, the key will be changed.

 

For example:

  [dbmaker@la-as4-s bin]$ ipcs

 

------ Shared Memory Segments --------

key                  shmid       owner      perms      bytes      nattch     status

0x0009c93b 294914     dbmaker   660        93249536   2

 

------ Semaphore Arrays --------

key        semid      owner      perms      nsems

 

[dbmaker@la-as4-s bin]$ ipcrm -m 294914

 

[dbmaker@la-as4-s bin]$ ipcs

 

------ Shared Memory Segments --------

key                  shmid      owner      perms      bytes      nattch     status

0x00000000 294914     dbmaker   660        93249536   1          dest

 

------ Semaphore Arrays --------

key        semid      owner      perms      nsems

 

  And, after have ipcrm the shared memory, when you have closed all related terminal, the shared memory will be remove.

  E.g. after closing all terminals and open a new one.

 

[dbmaker@la-as4-s bin]$ ipcs

 

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status

 

------ Semaphore Arrays --------

key        semid      owner      perms      nsems

 

You can see the shared memory 294914 has been removed.

Author: Sandy
Last update: 2016-03-08 09:11


Can we change the local code from Chinese to Japanese When upgrade DBMaker4.0.5 to 5.0? Any data truncate problem may cause?

Unload/load is text mode, so the data should be consistent, and DB_LCODE has no matter with data consistency.

Author: Sandy
Last update: 2016-03-08 09:12


Does DBMaker5.0 and DCI completely support the latest version ACUCOBOL?

DBMaker 5.0 DCI had applied SYSCOM internal system with ACUCOBOL 7.2.2.
This version is no problem. The latest ACUCOBOL version is 8.0, and this version we don't do any testing now.

Author: Sandy
Last update: 2016-03-08 09:14


Are there some limitations about keyword DB_NJNLB in Windows Platform? (DBMR 1882, version: DBMaker 5.0)

At present, DBMaker keyword DB_NJNLB has a limitation in windows platform; the below table shows the limitations of the current Windows OS.

 

DBMaker Journal Buffer (DB_NJNLB) Limitation on windows

DBMaker Version

Windows platform

Limitation

Description

DBMaker5.0

Windows 2000 server + sp4

63M

 

DBMaker5.0

Windows XP + sp2

63M

 

DBMaker5.0

Windows 2003 Server

63M

 

DBMaker5.0

Windows 2003 server + sp2

63M

 

DBMaker5.0

Windows Vista Ultimate

63M

 

DBMaker5.0

Windows 2008

63M

 

DBMaker5.0

DBMaker5.0 Bundle

Windows2003 64bit + sp2

31M

DB_NJNLB is influenced by the 64 bit 

Author: Sandy
Last update: 2016-03-08 09:37


Can you explain about setting parameter and saving setting in dmSQL? (From DBMR 1887)

After launching dmSQL, there are some default settings, such as AUTOCOMMIT, LOCK TIMEOUT, and LINEWIDTH and so on. Some of these settings are saved in the dmconfig.ini file and some of them are saved in DMSQLENV.INI file. So when starting dmSQL, these settings will be loaded from the dmconfig.ini and DMSQLENV.INI file.

When saving settings in dmSQL, these settings also will be updated into these two files, and dmSQL will load them when next starting.

In addition, dmSQL does not allow user to choose which parameter would be saved, so before save settings, user should check that all the current settings are the expected ones.

Author: Sandy
Last update: 2016-03-08 09:38


Can you explain the internal behavior of the online full backup? (DBMR: 1892, version: DBMaker 4.1 bundle)

 When perform the online full backup, the internal engine will:

  • Check Database status and journal files
  • set some internal setting in database
  • copy Database files to database backup directory
  • copy database journal files to database backup directory

Author: Sandy
Last update: 2016-03-08 09:41


Will online full backup impact the online transaction? (DBMR: 1892, version: DBMaker 4.1 bundle)

The main cost of the online full backup is IO, after checking database status and internal setting, database server will launch coping operation to start database files copying, and it will occupy system I/O.

Author: Sandy
Last update: 2016-03-08 09:42


What is the recommended interval value for both online full backup and database replication? (DBMR: 1892, version: DBMaker 4.1 bundle)

When setting Backup Schedule, user needs to consider how to set can make data lost less and can recovery when any types of database failure occurred.

The internal value depends on customer database busy or not, if very busy set shorter time interval. Please combine with actual system to set the value to prevent data losing.

Author: Sandy
Last update: 2016-03-08 09:43


Could you give us any explanation about how to install rollover.exe in the 4.1.6 bundle and how to use rollover.exe for full recovery step by step? (DBMR: 1892, version: DBMaker 4.1 bundle)

User must copy rollover.exe to the directory of 4.1.6 bundle to make sure that it locates in the same directory with other EXE files and DLL files.

The Backup Restore Guide has one chapter to explain how to use rollover tool.

Please refer to it.

If multiple journals exist in database, and user need to restore database to the last status before db crashed, specifying correct sequence for current multiple journal files is important, especially during rollover. The sequence is important and the order of journal files depends on the sequence in DB_JNFIL.

  For example:

  DB_JNFIL = jnl1.jnl jnl2.jnl jnl3.jnl

  So when rollover current journal files, jnl1.jnl is the 1-th one, jnl2.jnl is the 2-th one, jnl3.jnl is the 3-th one.

Author: Sandy
Last update: 2016-03-08 09:46


How to output query result with CSV format? (DBMR: 1893, version: DBMaker 4.3.2 bundle)

Export command only provides syntax with basic table,

EXPORT [INTO data-file]

            TABLE table-name

            [DESCRIPTION description-file]

            [LOG log-file]

            [STOP_ON_ERROR],

and does not support export query result.

Considering performance, they can create a view to replace that query result.

For example:

 (1) Create view v2 as select * from t1 inner join t2 on t1.c1 = t2.c3;

 (2) export into tmp.csv table v2 description tmp.des;

The view is dynamic and will be executed when be used, so exporting CSV format file will not do additional action, so the performance should be better than creating temporary table method.

Author: Sandy
Last update: 2016-03-08 09:49


How to use ESQL/C stored procedure in 4.3.3 bundle version? (DBMR 1912)

Suppose the HOME directory is: C:\dbmaker\43bundle.

It will use some folders and files in 4.3.3 normal version, so user needs to copy them to related bundle folders.

By the following steps user can prepare environment for building ESQL/C stored procedure with 4.3.3 bundle.

Steps:

(1)   Register DBMaker bundle in regedit: register HOME and COMPILER for bundle.

(2)   Copy dmppcc.exe to bundle\bin (C:\dbmaker\43bundle\bin)

(3)   Copy dmapi43.dll to bundle\bin (C:\dbmaker\43bundle\bin)

(4)   copy bundle dmconfig.ini to bundle\bin (C:\dbmaker\43bundle\bin)

(5)   Copy the templates folder to bundle (C:\dbmaker\43bundle\templates)

(6)   Copy include folder from normal to bundle (C:\dbmaker\43bundle\include)

(7)   copy lib folder from normal to bundle (C:\dbmaker\43bundle\lib)

(8)   Modify vc60.mak in templates folder: modify path to used folder name; if other folders or files are used in vc60.mak, these folders/files also need to be copied to bundle folder.

For example:

    C:\DBMaker\4.3\include => C:\dbmaker\43bundle\include

    C:\DBMaker\4.3\lib      => C:\dbmaker\43bundle\lib

Now, user can create and use ESQL/C stored procedure with 4.3.3 bundle.

Before that, please remove 4.3 normal versions to keep the environment clean.

Note: bundle dmconfig.ini and dmapi43.dll these two files exist in two folders, One is "C:\dbmaker\43bundle", another is "C:\dbmaker\43bundle\bin".

Author: Sandy
Last update: 2016-03-08 10:31


How to use Java stored procedure in 4.3.3 bundle version? (DBMR 1912)

Suppose the HOME directory is: C:\dbmaker\43bundle.

Workaround for building Java sp with 4.3.3 bundle as follows:

(1)   Copy jre folder from normal to bundle (C:\dbmaker\43bundle\jre)

(2)   Copy dmclassloader.jar to bundle\bin (C:\dbmaker\43bundle\bin)

(3)   Move bundle all jar files to bundle\bin (C:\dbmaker\43bundle\bin). Jar files are dmjdbc20xa.jar, dmjdbc20.jar, dmjdbc30.jar.

Note: If any one of these jar files exist in C:\dbmaker\43bundle folder, calling java sp will not execute any operation.

Now, user can create and use Java sp with 4.3.3 bundle.

Author: Sandy
Last update: 2016-03-08 10:33


Does DBMaker support the UPDATE/INSERT/DELETE with target table in subquery? (DBMR: 1913)

At present, DBMaker does not support the UPDATE/INSERT/DELETE with target table in subquery, but user can replace it by temporary table.

For example:

dmSQL> INSERT INTO uselgcsvcs

SELECT host_id, service_name, MAX(specification) as specification FROM usesvcs T WHERE NOT EXISTS (SELECT service_name FROM uselgcsvcs L WHERE T.host_id = L.host_id and T.service_name = L.service_name) GROUP BY host_id, service_name;

ERROR (6172): [DBMaker] cannot select into a table which has already been referenced in the query

Author: Sandy
Last update: 2016-03-08 10:35


How to set DSN for dbmaker4.3 bundle on 64bit? (DBMR: 1911, version: DBMaker 4.3 bundle)

TheOther settings are same with 32bit OS except two points:

(1)   user should register DBMaker4.3 and ODBC Driver in

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node] in regedit.

Node (Wow6432Node) is used to register 32bit software on 64bit OS.

(2)   It should use 32bit ODBC Data Source Administrator, but not 64 bit.

Default will open 64 bit, but not 32bit. 32bit Driver Manager is odbcad32.exe which locates in “C:\WINDOWS\SysWOW64”(suppose OS is Windows 2003 x64 Edition). Please use 32bit Driver Manager to create DSN.

We do not suggest user to use 32bit DBMaker on 64bit OS. We will publish DBMaker 64 bits version (DBMaker 5.1) soon.

Author: Sandy
Last update: 2016-03-08 10:37


Why the code of character data is different from the original character data after being Exported and Imported by the XMLExport and XMLImport? (DBMR 1917; version: DBMaker 4.X)

For DBMaker Japanese, the encoding of the XML file is Shift_JIS. Shift_JIS encoding has 2 types: 83pv and 90pv. If the original data is 83pv data, after being imported into database, the 83pv data would be translated to 90pv data because that DBMaker XMLImport will translate data into Unicode internally before inserting. At this time, the Unicode maps to 90pv type Shift_JIS by default.

For more detail information can refer to: http://homepage1.nifty.com/hm7/works/AppleScript/83pvSpChar-to-90pv.text

User can transfer 83pv to 90pv before inserting into database, because DBMaker 4.x series only accept 90pv Shift_JIS with XMLImport.

DBMaker 4.x series will adopt 90pv that is from Microsoft official announcement: http://support.microsoft.com/kb/170559/EN-US/, so we translate 83pv to 90pv.

DBMaker 5.x series will change to support 83pv and will not translate to 90pv, because 83pv is Japanese windows OS (CP932) adopting, it is commoner than 90pv.

For example:

suppose that customer’s original data (∈∋⊆⊇⊂⊃∪∩∧∨¬) is 83pv, hex encoding like below 81 B8 81 B9 81 BA 81 BB 81 BC 81 BD 87 9C 87 9B 81 C8 81 C9 FA 54.

After being imported into database, the 83pv data has been translated to 90pv data, the imported data 90pv hex encoding would be like below 81 B8 81 B9 81 BA 81 BB 81 BC 81 BD 81 BE 81 BF 81 C8 81 C9 81 CA.

Because DBMaker XMLImport will translate data into unicode internally then insert:

  1. get 83pv data '0x879c'  => translate to unicode - 0x222a  (because DBMaker use expat parser to parse xml file and expat parser only accept unicode data)
  2. Get unicode data - 0x222a and insert into database, it will translate unicode data - 0x222a into shift-jis; at this time, the unicode map to 90pv type shift-jis by default, so the data will be translated as '0x81be'. And ASCII code of data is different.
  3. No matter 83pv shift-jis (∩- 0x879c) or 90pv shift-jis(∩-0x81be) are all map to unicode - 0x222a, so the data view is the same in browser but the ASCII code number is different.

Author: Sandy
Last update: 2016-03-08 10:41


Does DBMaker on Linux support heterogonous Asynchronous Table Replication? (DBMR 1918)

 No, DBMaker released version does not support Heterogonous Asynchronous Table Replication in non-Windows platform currently.

For this limitation, DBA manual has the following description in "Heterogeneous Asynchronous Table Replication" chapter:

Due to the way DBMaker makes use of the ODBC Driver Manager to perform asynchronous table replication, the DBMaker server must be located on a computer running Windows NT or Windows 2000, and the definition of the destination database name cannot include a link name. The third-party destination databases may be located on Windows, UNIX, or Linux platforms...

Heterogonous Asynchronous Table Replication in Linux feature will be supported in future release of DBMaker and passed by fully testing of QA.

Author: Sandy
Last update: 2016-03-08 10:42


What is the relationship between the Keywords DB_ISOLV and DB_BROWS?

The influence strength of DBMaker lock option is: SQL command >connect option >dmconfig.ini setting.

If DB_ISOLV! = 2, 3, 4, DB_BROWS = 1 can work, if DB_ISOLV = 2 or 3 or 4, DB_BROWS is useless keyword.

The relationship between them is:

DB_ISOLV

DB_BROWS

1(default) 

0 (dirty read)

1 (select get S lock)

2(read committed)

Not work no matter 0 or 1

3(repeatable)

Not work no matter 0 or 1

4(Phantom)

Not work no matter 0 or 1

Author: Sandy
Last update: 2016-03-08 10:43


Can DBMaker work in following behavior? (DBMR: 1925; version: DBMaker 5.0)

insert

   Tran1                   Tran2

   ================  ==================

   ins c1 =1

                           ins c1=1 (lock)

   commit

                           Unique key violation (error)

 

 update

   Tran1                   Tran2

   ===============    ==================

                           select c1, get {1}

   upd c1 = 2 where c1 = 1

                           select c1, get {1}

   commit

                           select c1, get {2}

 

 delete

   Tran1                   Tran2

   ==============    ====================

   select c1, get {2}

                           delete where c1 = 2

   select c1, get {2}

                           commit

   select c1, get { }

For <insert>, Transaction 1 insert get X lock in data page structure ( OID + data),  Transaction 2 insert data also get X lock in different structure of data page (OID + data), in DBMaker the OID is always different, so it is different lock object in DB engine. So it does not return lock time-out.

For <update> and <delete>, DBMaker lock architecture does not support version lock design. It is DB engine kernel architecture, DBMaker does not support now.

Author: Sandy
Last update: 2016-03-08 10:47


Does DBMaker bundle version have the online full/incremental backup and restore database function? (DBMR 1935; version: DBMaker bundle)

DBMaker bundle 5.0 has included JTools.

Before 5.0, user needs to install JTools of standard version to recover database. JServer manager of normal DBMaker can be used as recover tool in DBMaker Bundle.  

Author: Sandy
Last update: 2016-03-08 10:50


Is it possible to zip/unzip for backup and recovery?

Because zip backup file feature should depend on backup server or JServer Manager Tool if setting DB_BKZIP on, so rollover (command line utility) does not support zip/unzip for backup and recovery.

Author: Sandy
Last update: 2016-03-08 10:51


Will these DBMaker routine transactions including online full/incremental backup and update statistics compete against each other?

All the DBMaker transactions depend on Journal system, and so do update statistics, online full /incremental backup also follow journal (undo/redo) to backup files, so
it's no problem in 'update statistics'.

Author: Sandy
Last update: 2016-03-08 10:52


How long does it take for full backup of 100G database? (Both zip and no zip) (DBMR 1935; version: DBMaker bundle 4.3.3)

 There are about 2 factors that will affects the full backup time, they are:

Data type: When doing full backup with zip, compressed situation is different according to different data situation. Ordinary data is better compressed than multimedia data.

Zip or no zip: because cost time between CPU and I/O is different. And full backup with zip mainly spends CPU, and full backup with no zip spends I/O, but the speed of CPU is much faster than I/O. Generally speaking, full backup with zip is better choice than with no zip. Except that the compressed scale is much bad.

 

In addition, we still tested two supposed situation. Due to limitation of hardware, the tested database was about 50G.

 

Please refer to the following table:

Windows

Time(unit:   second)

Size of   backup Dir

Empty DB(51.9G)

(DB_BkSvr = 1)

No zip backup

2848

51.9G

Zip backup

(DB_BKZIP = 1)

1796

115M

Insert some   ordinary and blob data(53.7G)

(DB_BkSvr = 1

DB_BKFOM =2

DB_BMODE =2)

No zip backup

3045

53.7G

Zip backup

(DB_BKZIP = 1)

2022

3.65G(including fo   dir)

Testing method: using JServer manager of normal 4.3.3 to test online full backup.

Author: Sandy
Last update: 2016-03-08 10:54


When running DBMaker perl sample program ("demo.pl") on DBMaker 5.0, what should be noticed by user?

 Suppose that version of DBD::DBMaker is 0.15:

 

1. Notice version of DBMaker: the original Makefile.PL may contain the entry at line12:

my $DEFAULTVER = '3.7';

Modify the entry as follows:

my $DEFAULTVER = '5.0';

2. User may meet: the "fetchrow_hashref" method in demo.pl program did not get correct result and it only shows "0" in each row

It’s a bug of demo.pl sample, because DBMaker 4.x and 5.x has updated the policy for case sensitive (IDCAP=1 default), so demo.pl should be modified as following:

$hash_ref->{"Id"},$hash_ref->{"Name"},$hash_ref->{"Title"},$hash_ref->{"Phone"});

==>

$hash_ref->{"ID"},$hash_ref->{"NAME"},$hash_ref->{"TITLE"},$hash_ref->{"PHONE

Author: Sandy
Last update: 2016-03-08 11:00


Why does the client side which is on Vista fail to connect to the server side that is on Windows XP sp2? (DBMR 1885; version: DBMaker 5.0)

It is because the tested DB server and client side used different LCode,so test returned errors including LCode were incompatible and Driver's SQLSetConnectAttr failed. To add DB_LCode in dmconfig.ini in both DB server and DB client and make sure the value is the same, it may connect successfully. 

Author: Sandy
Last update: 2016-03-08 11:02


Why does the error “option type out of range” return When importing data into Excel 2003 from DBMaker4.3.0? (DBMR 1930; version: DBMaker4.3.0)

It is a bug; we had fixed it after DBMaker 4.3.0. And we tested 4.3.1, 4.3.2 and 4.3.3, they were all ok. User can try to upgrade DBMaker and test again.

In addition, for the error “option type out of range “, generally speaking, it should be related with connection string. If connection string includes unsupported option or some garbage at the end of string, it may return this error some time, but not all the time.

Author: Sandy
Last update: 2016-03-08 11:03


How does DBMaker act if network disconnection happens during inserting process? (DBMR 1945)

If the network disconnection happens during inserting process, the current disconnected connection may remains on server and the session still occupied the resource. The session exists, but it becomes useless session. DBMaker does not automatically release resource of the dead connection. So user might get lock timeout error. User should kill these useless sessions to release resource.

If network disconnection happens before committing a transaction, after killing the dead connection, the aborted transaction will be roll backed.

Author: Sandy
Last update: 2016-03-09 02:56


When compiling PHP with DBMaker, the error “undefined reference to ´__ctype_b'” happens, what is the reason and how to do? (DBMR 1946, Version; DBMaker 4.2)

This error is not DBMaker's problem; it is from Linux glibc version issue. DBMaker use glibc-2.1 to make our static library, but glibc-2.3 removes some functions - _ctype_b(), _ctype_toupper()…And glibc ensures binary compatibility for shared libraries only not include static library, so static library is not ensured to be compatibility, that is the reason of undefined reference to ´__ctype_b' when link glibc-2.3.

Doing the following actions to solve this problem:

1. Moving /home/dbmaker/4.2/lib/libdmapic.a to other folder.

2. Copying /home/dbmaker/4.2/lib/so/libdmapic.so into /home/dbmaker/4.2/lib/

3. ./configure --with-apxs2=/usr/local/apache/bin/apxs --with-dbmaker=/home/dbmaker/4.2  --enable-track-vars  --enable-mbstring

4. make

This is link .so solution; other customers had make success and apply into their system.

Author: Sandy
Last update: 2016-03-09 03:00


Does DBMaker have the solution for Oracle syntax like “SELECT id FROM groups START WITH id=2 CONNECT BY PRIOR parent_id = id”, if not is there some other method to get the same result? (DBMR1954)

DBMaker doesn't support recursive SQL, but it can get same result by splitting several SQL.

For example, Customer can via temp table and run following steps:

drop table t1;

drop table t2;

create table t1 (c1 int, c2 int);

create table t2 (c1 int, c2 int);

 

insert into t1 values (1, 11);

insert into t1 values (1, 12);

insert into t1 values (2, 21);

insert into t1 values (11, 111);

insert into t1 values (11, 112);

insert into t1 values (12, 121);

insert into t1 values (12, 122);

insert into t1 values (12, 123);

insert into t1 values (13, 131);

insert into t1 values (14, 141);

insert into t1 values (121, 1211);

insert into t1 values (123, 1231);

insert into t1 values (131, 1311);

insert into t1 values (1231, 12311);

insert into t1 values (12311, 123111);

 

/*

SELECT c1 FROM t1

   START WITH c1=1 CONNECT BY

    PRIOR c1 = c2;

 

RecursiveQuery(sqlcmd, startwith, prior)

 

RecursiveQuery("SELECT c1 FROM t1", "c1=1", "c1=c2") */

 

drop table tt1;

drop table tt2;

create table tt1 (c1 int, c2 int);

create table tt2 (c1 int, c2 int);

 

delete from tt1;

select c1, c2 from t1 where c1=1 into tt1; select * from tt1 into t2;

 

// loop begin

 

delete from tt2;

select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;

 

delete from tt1;

select t1.c1, t1.c2 from t1, tt2 where tt2.c2=t1.c1 into tt1; select * from tt1 into t2;

 

delete from tt2;

select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;

 

delete from tt1;

select t1.c1, t1.c2 from t1, tt2 where tt2.c2=t1.c1 into tt1; select * from tt1 into t2;

 

delete from tt2;

select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;

 

// loop end until select into no rows

 

drop table tt1;

drop table tt2;

 

select distinct c1 from t2;

Also, DBMaker SP can help:

First, create the sp connby.ec

/*

SELECT id FROM groups

  START WITH id=2 CONNECT BY

    PRIOR parent_id = id;

*/

 

exec sql create procedure connectby_groups (integer hid) returns int outid;

{

exec sql begin declare section;

int i, cnt;

char sqlstr[256];

exec sql end declare section;

 

exec sql begin code section;

 

$ whenever sqlerror continue;

exec sql drop table temp_connectby;

$ whenever sqlerror goto dmSP_EXIT_LABEL;

 

exec sql create temp table temp_t1 (id int, parent_id int);

exec sql create temp table temp_t2 (id int, parent_id int);

exec sql create temp table temp_connectby (id int, parent_id int);

 

exec sql select id, parent_id from groups where id=:hid into temp_t1;

exec sql select id, parent_id from temp_t1 into temp_connectby;

 

do {

   exec sql delete from temp_t2;

   exec sql select a.id, a.parent_id from groups a, temp_t1 b where a.id=b.parent_id into temp_t2;

   exec sql select count(*) from temp_t2 into :cnt;

   if (cnt > 0)

      exec sql select id, parent_id from temp_t2 into temp_connectby;

   else

      break;

 

   exec sql delete from temp_t1;

   exec sql select a.id, a.parent_id from groups a, temp_t2 b where a.id=b.parent_id into temp_t1;

   exec sql select count(*) from temp_t1 into :cnt;

   if (cnt > 0)

      exec sql select id, parent_id from temp_t1 into temp_connectby;

   else

      break;

   } while (1);

 

exec sql RETURNS select distinct id from temp_connectby into :outid;

 

exec sql drop table temp_t1;

exec sql drop table temp_t2;

exec sql end code section;

}

Then run the sample SQLs:

create db sample5;

 

create table groups (id int, parent_id int);

 

insert into groups values (1, null);

insert into groups values (11, 1);

insert into groups values (12, 1);

insert into groups values (13, 1);

insert into groups values (111, 11);

insert into groups values (112, 11);

insert into groups values (131, 13);

insert into groups values (1121, 112);

insert into groups values (11211, 1121);

insert into groups values (112111, 11211);

 

terminate db;

 

start db sample5 sysadm;

 

create proc from 'connby.ec';

 

call connectby_groups(112111);

call connectby_groups(1);

call connectby_groups(131);

call connectby_groups(168);

call connectby_groups(11211);

 

terminate db;

Author: Sandy
Last update: 2016-03-09 03:12


What is the file databasename.pid used for? (DBMR1955; version: DBMaker4.3)

File DBName.pid keeps dmserver running process id.

It's for using ‘dmserver –k’ to shutdown database, we need to know which process id is catched by dmserver, because we cannot get shared memory of 'dmserver -k' mode in shutdown, so keep this file for reference of 'dmserver -k' shutdown.

This file is useless if executing “terminate db” by dmSQL to shutdown database, it is ONLY for ‘dmserver –k’ mode.

Author: Sandy
Last update: 2016-03-09 03:39


Some customers experienced the error 11206 when starting the database as service on Windows 2003. If the server machine is rebooted, the problem disappears, and the service runs correctly, but when someone stops the service and re-starts it, the error occurs and exists until the machine is rebooted again,why?(DBMR1957; version:DBMaker 4.3)

First, please check whether antivirus software prevents DBMaker accessing db files, which can result in starting db failure with error 11206. The similar issue is DBMR1737

Author: Sandy
Last update: 2016-03-09 03:42


What's the difference between error 11206, 11210 and 11213? (DBMR1957; version:DBMaker 4.3)

User can distinguish them by their letter.

Error 11213: cannot find file or directory in related directory.

Error 11210: cannot have permission to access this file

Error 11206: cannot create/open a file.

When starting db, DBMaker will access db files, so it needs to find files in related db directory, and if it does not find one, error 11213 occurs; or else, if also not having permission to access this file, error 11210 occurs. Error11206 does not only include the two situations, also include other unknown causes which result in operation a file failure.

If meeting any one of them, customer should consider above all reasons which result in operation file/directory failure.

Author: Sandy
Last update: 2016-03-09 03:43


How to successfully create stored procedure from 03mail.ec which is installed with DBMaker samples in the StoreProc folder? (Version; DBMaker 4.3)

For using 03mail.ec correctly, user needs to link wsock32.lib:

First, Modify templates\vc60.mak as follows:

LINK32_FLAGS=wsock32.lib C:\DBMaker\4.3\lib\dmudf.lib

Then, create procedure from 'C:\dbmaker\4.3\samples\StoreProc\03mail.ec';

It will be ok.

Author: Sandy
Last update: 2016-03-09 03:46


How to export data to CSV file from DBMaker?

DBMaker supports the export function; user can refer the chapter 7.1 of Backup Restore Guide42.doc for detail.

Author: Sandy
Last update: 2016-03-09 03:58


2009

How to hide the dmsql tool window to final user?

To hide the dmsql window, developer can add the following command to script file.

d:\dbmaker\5.0\bin\dmSQL32.exe /b

The parameter /b can help to hide console, then remember to add quit in the script file, otherwise this process always alive until you kill from task manager.

For example,

executing dmsql32.exe /b test.sql

The content of test.sql as following:

connect to dbsample5 sysadm;

set linewidth off;

set printto dbinfo.out;

select * from SYSINFO;

select * from SYSUSER;

set printto off;

disconnect;

q;

Author: Sandy
Last update: 2020-06-19 04:42


What is md5.dll, and how use MD5 related functions?

md5.dll is the dll file of UDF in sample. Users also can create the dll by themselves.

For detailed, please refer to README.TXT in ‘DBMaker InstallDir\samples\udf’ folder.

Author: Sandy
Last update: 2016-03-09 04:18


What does the ITT stand for and what's the correct range of values for it?

 ITT -- Internal Temporary Table: for sorting/ordering table in table join, select ... order by, etc.

ITT uses physical memory, not use the DCCA. DBMaker has a smart mechanism which will calculate the suitable memory usage in your physical memory, if the usage is over the ITT in the memory, it will use temp file.

Information of ITT in JDBA Tool is queried from SYSINFO as follows:

0716 MAX_ITT_SIZE: firstly, detect OS free memory, and then assign DBMaker internal formula calculating result value

0717 CURRENT_ITT_SIZE: current used ITT memory

ITT values must be positive value, showing negative value is abnormal, and it may be caused by not detecting free memory or incorrect values used in formula.

Author: Sandy
Last update: 2016-03-09 04:20


Does DBMaker support HATR on Linux? (DBMR1918; version:DBMaker 5.0.1)

DBMaker supports HATR (Heterogeneous Asynchronous Table Replication) feature, but DBMaker server must be located on a computer which is running on Windows. For DBMaker uses the ODBC Driver Manager to perform HATR.

Now, DBMaker 5.0.1 provides a new feature: HATR can work in Linux and it is express.

For HATR in windows, please refer to DBA manual.

For HATR in Linux, please contact DBMaker support to refer to How to use HATR Feature.doc.

Author: Sandy
Last update: 2016-03-09 04:21


Does DBMaker bundle have limitation on the database size when use zip backup function? (DBMR1968; Version: DBMaker 4.3.4 bundle)

About zip backup, DBMaker does not have limitation on the database size. But we only have tested for 100G database for the disk space reason.

Please note if OS has problem or limitation, applications which are running on the OS will also have the issue.

Author: Sandy
Last update: 2016-03-09 04:23


If the customer set specific time for PITR like 02/10 10:00:00, dbmaker can recover the database to the exact time? (DBMR1968; Version: DBMaker 4.3.4 bundle)

Yes, DBMaker can restore the database to the exact time (DB_RTime).

When doing recover with setting DB_RTime, dbmaker will compare time of journal records in each journal file, if time of journal record is before DB_RTime, journal record will be restored. For uncommitted transaction, it will do rollback.

Author: Sandy
Last update: 2016-03-09 04:59


Does DBMaker remove existed incremental backup file after next full backup? (DBMR1968; Version: DBMaker 4.3.4 bundle)

Before 5.0 not including 5.0, DBMaker never removed existed incremental backup files in DB_BKDIR. But user found these incremental backup files is useless after a new full backup (MR 8121).So we remove previous useless incremental backup files after a new full backup on DBMaker 5.0.

No matter you set DB_BkOdr keyword or not, 5.0 must remove previous incremental backup files in DB_BKDIR. If set DB_BkOdr keyword, previous incremental backup files will be copied to DB_BkOdr. It means 5.0 will remove or backup previous incremental backup files before doing the last full backup.

5.0 do some change for backup, for detailed, please refer to related manual.

Author: Sandy
Last update: 2016-03-09 05:01


Can we change the directory of incremental backup file? (DBMR1968; Version: DBMaker 4.3.4 bundle)

No. Incremental backup files must locate in DB_BKDIR. For 5.0 and after version, DB_BkOdr not only stores previous version of full backup files, but also stores previous version of Incremental backup files.

Author: Sandy
Last update: 2016-03-09 05:02


How to backup and restore database with online full backup in bundle version? (DBMR1973; version:DBMaker bundle 4.3.4)

There are two methods to backup and restore database in bundle version.

The one is using the Jserver Manager tool, but if they only installing DBMaker client, they can not use JServer manager then. The two functions backup and restore can only be used on DBMaker Server side, but not on DBMaker client side.

The other is using the rollover tool which is supported by normal version. The rollover tool is located in bin directory of DBMaker4.3.4 normal.

Author: Sandy
Last update: 2016-03-09 09:10


How to use OID in the program? (DBMR1974; version:DBMaker 4.2 in Linux)

The OID is a special data type. Although it is possible to use an OID in a query to select data from a table and then use the OIDs to update the table data, this is not common practice when using the SQL language. OIDs are usually used in the internal programming interface.

So please not directly use OID in program, otherwise, it may be cause some strange codes sometimes.

In fact, they only need to get object name related with this OID.

For example:

select USER_NAME, TABLE_NAME, SYSLOCK.LK_GRAN, SYSLOCK.HOLD_LK_CONNECTION,

SYSLOCK.LK_CURRENT_MODE from SYSLOCK,SYSTEM.SYSTABLE,SYSUSER where

HOLD_LK_CONNECTION = CONNECTION_ID and LK_OBJECT_ID = TABLE_OID and

TABLE_OWNER != 'SYSTEM';

Author: Sandy
Last update: 2016-03-09 09:12


is there some Notices for upgrade DBMaker 4.3 to 5.1 in application side?(DBMR1975; version:DBMaker 4.3)

Notice in application side:

  • ODBC Driver: if application refers to Driver name (such as “Driver={DBMaker  4.3 Driver}…”), user should modify it to 5.1 driver.
  • DSN: if application refers to DSN, user should recreate the DSN by 5.1 Driver.
  • If referred to dmapi43.lib, user should replace it with dmapi51.lib; If referred to 4.3 include file, user should replace it with 5.1 include file.
  • JDBC Driver: if used CLASSPATH and LD_LIBRARY_PATH environment variable, user should modify them to refer to 5.1 related files.
  • DDL: When used DDL, user should adjust long of each column depending on UTF8 DB; And if migrated db is 5.0 and after 5.0 versions, user should notice limitations of object naming.

Author: Sandy
Last update: 2016-03-09 09:13


Can you help to setup DBMaker 4.3.4 bundle version for Chinese?

DBMaker 4.3.4 bundle version in Chinese is not DBMaker official product line, we can provide, but it will adopt express fix policy. And in this version, only error message is simplified Chinese.

DBMaker 5.1 bundle support english / simplified chinese / tranditional chinese error message issue, the official download version can support customer request.

ftp://www.dbmaker.com.tw/pub/DBMaker/5.1.0J/dbmaker-5.1.0BundleJTools-WIN32.zip

Add db_errlcode keyword in dmconfig.ini of client site.

English: DB_ERRLCODE = EN.ASCII

Simplified Chinese: DB_ERRLCODE = ZH_CN.GBK

Traditional Chinese: DB_ERRLCODE = ZH_TW.BIG5

The client connection will check DB_ERRLCODE setting and correct error message locale return DB_ERRLCODE=<string>.This keyword is used to set client's error message character set. In multilingual database, client side can set their own output locale codes of error message. Error table is put in dbmaker/5.1/shared/locale/locale.lang/ directory. The value of the keyword can be the combination of the local definition and the character set. User can use the command SELECT GETSYSINFO('ERRLCODE') to return the error message character set of the client side.

Author: Sandy
Last update: 2016-03-09 09:14


How to use DBMaker function TO_DATE (string_expr, date_format_string)?

TO_DATE (string_expr, date_format_string) is used to convert a selected string to DATE format. The selected string can be of any data type, but it must conform to a valid date when converted to date.

The TO_DATE function consists of two parameters: string_expr and date_format_string. string_expr represents the string that is to be matched, while the date_format_string represents the format that the Date type data result set will take.

The TO_DATE UDF function is in DBMaker's installation directory\shared\udf, it's not created by default in the database. If users want to use it, they have to create it manually.

To excute the following command to create this function:

create function to_date.TO_DATE(varchar(20), varchar(20)) RETURNS DATE;

The example to use TO_DATE:

select to_date('2009-Jan-01', 'YYYY-mon-DD');

select to_date('2009-1-01', 'YYYY-MM-DD');

select  to_date('Feb/29-2000','MoM/DD-YYYY');

Author: Sandy
Last update: 2016-03-09 09:34


2010

What is the influence of group commit when DBMaker install on a high-end machine specification? (DBMR: 1922 DBMaker 4.2 OS: CentOS 4.0)

When we set DB_GCXMW = 0 in dmconfig.ini will turn off group commit feature and don't let db server let connection sleep. When db server ready to do group commit will let some connect sleep then commit together. But dbserver cannot wake up sleeped connection. It maybe cause client command stays in 'EXDIR' Status.

Version: DBMaker 4.x, 5.x

Product: Normal, Bundle

Platform: Linux

Author: Sandy
Last update: 2016-03-09 09:38


When DBMaker will use ITT?

ITT Internal Temporary Table is DBMaker internal database object that it’s used in memory table, query sort (like - group by, order by, merge join).its lifecycle is one transaction begin to end. ITT will be deleted when transaction ended.

Version: DBMaker 4.x, 5.x

Product: Normal, Bundle

Platform: Linux, Windows

Author: Sandy
Last update: 2016-03-09 09:39


How about DBMaker 5.1 support English / simplified Chinese / traditional Chinese error message issue?

add db_errlcode keyword in dmconfig.ini of client site

English: DB_ERRLCODE = EN.ASCII

Simplified Chinese: DB_ERRLCODE = ZH_CN.GBK

Traditional Chinese: DB_ERRLCODE = ZH_TW.BIG5

The client connection will check DB_ERRLCODE setting and correct error message locale return DB_ERRLCODE=<string> this keyword is used to set client's error message character set. In multilingual database, client side can set their own output locale codes of error message.

Error table is put in dbmaker/5.1/shared/locale/locale.lang/ directory.

The value of the keyword can be the combination of the local definition and the character set.

User can use the command SELECT GETSYSINFO ('ERRLCODE') to return the error message character set of the client side.

Version: DBMaker 5.1

Product: Normal, Bundle

Platform: Linux, Windows

Author: Sandy
Last update: 2016-03-09 09:41


What should we Notice for upgrade dbmaker 4.3 to 5.1 in applications side (DBMR: 1975)

Notice in application side:

ODBC Driver: if application refers to Driver name (such as “Driver= {DBMaker 4.3 Driver}…”), user should modify it to 5.1 driver.

DSN: if application refers to DSN, user should recreate the DSN by 5.1 Driver.

If referred to dmapi43.lib, user should replace it with dmapi51.lib;

If referred to 4.3 include file, user should replace it with 5.1 include file.

JDBC Driver: if used CLASSPATH and LD_LIBRARY_PATH environment variable, user should modify them to refer to 5.1 related files.

DDL: When used DDL, user should adjust long of each column depending on UTF8 DB;

And if migrated db is 5.0 and after 5.0 versions, user should notice limitations of object naming.

NOTE: DBMaker5.1 has added a new keyword DB_CLILCODE, when use multilingual database and the LCODE of the Database Server is UTF-8, client side can use several local codes to connect to the UTF-8 database server.

Version: DBMaker 5.1

Product: Normal, Bundle

Platform: Linux, Windows

Author: Sandy
Last update: 2016-03-09 09:43


how to avoid garbage character problem with UTF8 DB in php program? (DBMR: 1977)

The reason is data in the php pages was UTF-8 encoding. When the data was sent to driver manager, driver manager found the DBMaker supported unicode ODBC function, so driver manager treated the UTF-8 data as local code (OS default language encoding, ex: Shift-JIS in Japanese OS) and converted to unicode. At this moment the converted unicode data were wrong data, so the wrong data saved to DBMaker cause garbage data in and garbage data out after fetched from DBMaker.

Solutions: Using DBMaker ANSI driver, it avoids driver manager trying to convert the UTF-8 data to unicode. You modify the register file (dbmaker51_ansi_driver.reg) to suitable setting before installing it.

ANSI drive register file content includes:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DBMaker 5.1 ANSI Driver]

"Driver"="d:\\DBMaker\\5.1\\bin\\DMAPI51a.DLL"

"Setup"="d:\\DBMaker\\5.1\\bin\\DMSET.DLL"

"APILevel"="1"

"ConnectFunctions"="YYN"

"CPTimeout"="60"

"DriverODBCVer"="03.00"

"FileUsage"="0"

"SQLLevel"="1"

The steps include:

(1) Modify DBMaker 5.1 ANSI driver name if it's not 5.1

(2) modify Driver and Setup installed directory

(3) Modify dmapi51a.dll to dmapixxa.dll if your version is not 5.1

Therefore, if you are using DBMaker 5.1 what you need to do is only (2).

Please remember remove the DSN (default is Database name) from driver manager and use "DBMaker 5.1 ansi Driver" to register DSN again. The DBMaker ANSI driver is existed after installation (dmapi51a.dll); you only need to register it. The DBMaker ANSI driver only removes the unicode ODBC function.

Version: DBMaker 5.1

Product: Normal, Bundle

Platform: Windows

Author: Sandy
Last update: 2016-03-09 09:45


What about JDBC connection support parameters?

JDBC connect string not only include connect information but also support some database keyword settings. In DBMaker connect string keyword support UID, PWD, SVADR, PTNUM, ATCMT, CTIMO, DIFCO, STRSZ, STROP, DSCMT, ERRLCODE, CLILCODE keyword currently.

LTIMO(5.4.3 #29936, 20200708)

We will introduce how to write JDBC Connection String and give simple sample to illustrate the usage for these keywords:

JDBC Connection String:

URL=" jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;uid=sysadm;pwd=abc; atcmt=1; strsz=1000; strop=1; dscmt=1; errlcode=JA; clilcode=Shift-JIS;”

Conn = DriverManager.getConnection (URL);

For example:

UID, PWD

 Write UID and PWD in URL string and getConnection (url) by url one parameter. Program connects to database successfully by this way.

Test Program:

public class KeyWords {

    static String driver ="dbmaker.sql.JdbcOdbcDriver";

    static String url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;uid=sysadm;pwd=;";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

                     Class.forName (driver);

                     Connection conn = DriverManager.getConnection (url);

                     try{

                                System.out.print ("Connect Successful!");

         }

         catch (Exception e){

               e.getStackTrace ();

         }

    }

}

ATCMT: we can obtain the Auto-commit status by getAutoCommit () function.

Test program:

public class KeyWords {

    static String driver ="dbmaker.sql.JdbcOdbcDriver";

    static String url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;ATCMT = 0;";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

         Class.forName (driver);

         Connection conn = DriverManager.getConnection (url, "SYSADM", "");

         try{

               Boolean i = conn.getAutoCommit ();

               System.out.print("AUTO-Commit-STAUS:"+i+"\n");

                     }

         catch (Exception e){

               e.getStackTrace ();

         }

    }

}

Step (1): Set ATCMT = 0 in url

Result in Console:

AUTO-Commit-STAUS:false

Step (2): Set ATCMT = 1 in url in above program.

Result in console:

AUTO-Commit-STAUS:true

 

DSCMT: This keyword specifies whether to commit a transaction when an application is disconnecting from the database

Test Program:

public class KeyWords {

    static String driver ="dbmaker.sql.JdbcOdbcDriver";

    static String url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;ATCMT = 0;DSCMT = 0;";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

         Class.forName(driver);

         Connection conn = DriverManager.getConnection (url, "SYSADM", "");

         try{

               Boolean i = conn.getAutoCommit ();

               System.out.print("AUTO-Commit-STAUS:"+i+"\n");

            Statement stmt = conn.createStatement ();

            int effctCount = stmt.executeUpdate("UPDATE jobs SET JOB_DESC='Snow,Modify,DBMaker-test' WHERE JOB_ID = 1");

            System.out.println ("The Modified Rows Count is :"+effctCount+"\n");

            ResultSet rs = stmt.executeQuery ("select * from jobs");

            rs.next ();

            String s1= rs.getString (1);

            String s = rs.getString ("JOB_DESC");

            System.out.print ("The Modified\nJOB_ID:"+s1+"JOB_DESC:"+s);

         }

         catch (SQLException e){

               e.getStackTrace ();

         }

    }

Step (1): DB_DSCMT keyword act on auto-commit off status, DBMaker decides to whether execute commit command when a client's application issues a SQLDisconnect.so we should set ATCMT = 0 before test keyword DSCMT.

 URL = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;ATCMT = 0; DSCMT = 0;”

Step (2): Execute UPDATE statement in AP and Query the result from DBSAMPLE5 to examine the modified result whether affected in database.

Step (3): Query the result from dmSQL and we will find the new data which modified via AP can’t affect in database. This proves DSCMT = 0 is an available setting in URL.

dmSQL> select * from jobs where JOB_ID = 1;

 

  JOB_ID                         JOB_DESC                      MIN_LVL MAX_LVL

=========== ================================================== =======

          1 New Hire - Job not specified                            10      10

Step (4): set DSCMT=1, execute STEP (2) and STEP (3) again. We will find the new data which modified via AP had been affected in database too. This proves DSCMT = 1 is an available setting in URL.

dmSQL> select * from jobs;

  JOB_ID                     JOB_DESC                      MIN_LVL MAX_LVL

=========== ================================================== =======

      1    Snow, Modify, DBMaker-test                                10      10

 

 

ERRLCODE: you can set ERRLCODE = en, ja, zh_TW, zh_CN, UTF-8 etc which supported by DBMaker.

Test Program: we can get the ERRLCODE error message character set by function GETSYSINFO().

public class KeyWords {

    static String driver ="dbmaker.sql.JdbcOdbcDriver";

    static String url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;ERRLCODE =zh_CN;";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

         Class.forName (driver);

         Connection conn = DriverManager.getConnection (url, "SYSADM", "");

         try {

               Statement stmt = conn.createStatement ();

           ResultSet rs = stmt.executeQuery ("SELECT GETSYSINFO ('errlcode')");

               rs.next ();

               String s = rs.getString (1);

               System.out.print ("ERRLCODE:"+s);

Step (1): set ERRLCODE =zh_CN in url.

Restlt in console:

ERRLCODE:zh_CN 

Step (2): set ERRLCODE =en in URL.

Restlt in console:

ERRLCODE:en

 

CLILCODE: This keyword specifies the language code in the client side. When use multilingual database and the LCODE of the Database Server is UTF-8, client side can use several local codes to connect to the UTF-8 database server. These codes can be BIG5, EUC-JP, and UTF-8 etc which supported by DBMaker.but it the Server LCODE is not UTF-8, the default value would be same with server LCODE.

Test program: Use the same Test Program with above which tested keyword ERRLCODE and only need do following change:

Replace Test Program of “ResultSet rs = stmt.executeQuery ("SELECT GETSYSINFO ('errlcode')");” with “ResultSet rs = stmt.executeQuery ("SELECT GETSYSINFO ('clilcode')");”

Replace URL of ERRLCODE (ERRLCODE=zh_CN) with CLICODE = EUC-JP OR CLILCODE=GBK.

Step (1) set CLILCODE = EUC-JP in url, url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;CLILCODE =EUC-JP;";

Result in console:

CLILCODE: EUC-JP  

Step (2) set CLILCODE = GBK, url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;CLILCODE =GBK;";

Result in console:

CLILCODE: GBK 

 

STROP: This keyword specifies whether space padding is removed before applying the string concatenation. A value of 0 indicates space padding for fixed length CHAR type data is kept before applying the string concatenation operator. A value of 1 indicates the space padding is removed before applying the string concatenation operator.

Test program: Use the same Test Program with above which tested keyword ERRLCODE and only need do following changes:

Replace Test Program of “ResultSet rs = stmt.executeQuery ("SELECT GETSYSINFO ('errlcode')");” with “ResultSet rs = stmt.executeQuery ("SELECT CONCAT ('aaa    ','bbbb')");

Replace URL of ERRLCODE (ERRLCODE=zh_CN) with STROP = 0 or STROP = 1  

 

Step (1): set STROP=0 in URL: url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;STROP=0";

Result in Console:

aaa    bbbb

Step (2): set STROP=1 in URL: url = "jdbc:dbmaker://127.0.0.1:2453/DBSAMPLE5;STROP=1";

Result in console:

Aaabbbb

Author: Sandy
Last update: 2020-07-09 15:41


What’s parameters setting and reference sequence for application program access DBMaker? (DBMR2057)

Connection String not only include the essential information for connect to Database but also include some correlative connection option for Database setting, such as LCODE Database Character encoding setting, The Transaction isolation level(SQL_TXN_ISOLATION) setting, Auto-Commit mode status on or off (SQL_AUTOCOMMIT) setting ,etc.

SQLSetConnectOption function used to set connection option, For example:

You can set CLILCODE use keyword in dmconfig.ini or “set CLIENT_CHAR_SET ' Character Encoding' in AP.

For example:

In dmconfig.ini:

DB_CLILCODE=GBK;

In PHP program:

SET CLIENT_CHAR_SET GBK;

You can choose one of the methods, using the dmconfig keyword DB_IsoLv or set in AP by “SQLSetConnectOption SQL_TXN_ISOLATION”.

For example:

In dmconfig.ini

DB_ISOLV = 1 ;( 1,2,3,4 four options)

In ODBC program:

SQLSetConnectOption (hdbc, SQL_TXN_ISOLATION, SQL_TXN_ READ_UNCOMMITTED)

SQLSetConnectOption (hdbc, SQL_TXN_ISOLATION, SQL_TXN_READ_COMMITTED)

SQLSetConnectOption (hdbc, SQL_TXN_ISOLATION, SQL_TXN_REPEATABLE_READ)

SQLSetConnectOption (hdbc, SQL_TXN_ISOLATION, SQL_TXN_ SERIALIZABLE)

You can set in AP by “SQLSetConnectOption “function to set SQL_AUTOCOMMIT.

In ODBC program:

SQLSetConnectOption (hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

SQLSetConnectOption (hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);

If these connection option information are included on connection string, application program will be access Connection String directly, otherwise access the information from dbmconfig.ini.

 

Version: DBMaker 5.1.1

Product: Normal, Bundle

Platform: Windows

Author: Sandy
Last update: 2016-03-09 10:02


How to check the database situation from error message and find the solution when LOCK TIME-OUT occurs?

1) Check error message:

Lock time-out is normal behavior for Database, the cause of Lock time-out is waiting time beyond the maximum time setting when competition exists in multiple transactions. (Timeout interval default value is 5 seconds, you cans use “set lock timeout n seconds” reset the interval, also can set DB_LTimO keyword in dmconfig.ini and then restart database to apply)

We can check the Lock situation via following ways.

One way is you can set keywords in dmconfig.ini

DB_LGDIR=<you log path>

DB_LGLCK=1

DB_LGSVR=3 ;( 1......6)

If you want to more information you can set DB_LGSVR>3 and you can check the error message about lock time-out in directory which specified by DB_LGDIR When lock time-out occur.

View the error message:

File “db_name_data.LOG”:

LOG_TIME,BEG_TIME,STATE,RETCODE,EXE_TIME,SV_FUNC,CONNECT_ID,USERNAME,LOGIN_TIME,LOGIN_ADDR,STMT_ID,ERROR_ARG,OTHER_INFO,SQL_CMD

"2010/06/12 15:01:29","2010/06/12 15:01:24","X", 1102,5.01,"EXECUTE ", 3868,"SYSADM","2010/06/12 14:59:56","local",0,": Object ID : 2.18.12 ,Connection ID : 1976, LKMode : X   .NONE, LKStatus : GRANTED, Command : [EXIT]  insert into t1 values(16,'Skirt') [unknown  1096], 501507, 0, 0","INFO_03868_1","update t1 set c2 = 'Hello' where c1 = 16"

"2010/06/12 15:07:25","2010/06/12 15:07:25","X", 6151,0.00,"PREPARE ", 1976,"SYSADM","2010/06/12 14:59:52","local",0,": 123 [bisort.c 294], 122, 2, 0",,"select * from t1 order by 123"

"2010/06/12 15:07:34","2010/06/12 15:07:34","X", 6151,0.00,"PREPARE ", 1976,"SYSADM","2010/06/12 14:59:52","local",0,": 3 [bisort.c 294], 2, 2, 0",,"select * from t1 order by 1,2,3"

 

File “db_name_data.TXT”:

INFO_03868_1 --------------------

[ERR_LK_TIMEOUT]:: Object ID : 2.18.12 ,Connection ID : 1976, LKMode : X   .NONE, LKStatus : GRANTED, Command : [EXIT]  insert into t1 values(16,'Skirt')

The other way is select the Lock time-out information from SYSLOCK, SYSTABLE, and SYSYUSER.

For example,

dmSQL> update t1 set c2 = 'Hello' where c1 = 16;

ERROR (1102): [DBMaker] lock time-out: Object ID: 2.18.12, Connection ID: 1976, LKMode: X   .NONE, LKStatus: GRANTED, Command: [EXIT] insert into t1 values (16,'Skirt')

 

Lock time-out occur, Object ID = 2.18.12 is occupied by 1976 and status is X (nobody can get Object: 2.18.12 until 1976 connection release), this cause subsequent can’t get correct information for DB and Lock time-out occur.

Analyze the reasons for lock time-out:

First, execute the following SQL statement and know what connection caught database resources.

dmSQL> select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER, TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN! ='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1, 2, 3, 4, 5, 6;

 

HOLD_LK_CONNECTION  LOGIN_IP_ADDR                  LOGIN_HOST                     LOGIN_TIME                                                                 USER_NAME                                                                                                                       TABLE_OWNER                                                                                                                       TABLE_NAME                                                            LK_NEW_MODE LK_GRAN LK_STATUS LK_CURRENT_MODE   TIME_OF_SQL_CMD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

                                        SQL_CMD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

=======================================================================

              1976 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:52  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   TABLE   GRANTED   IX  .NONE       2010/06/12 15:01:00  [EXIT]  insert into t1 values(16,'Skirt')                                                                                                                                                                                                                                                                                                                                                                                                                                           

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

              1976 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:52  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   TUPLE   GRANTED   X   .NONE       2010/06/12 15:01:00  [EXIT]  insert into t1 values(16,'Skirt')                                                                                                                                                                                                                                                                                                                                                                                                                                           

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

              1976 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:52  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   PAGE    GRANTED   IX  .NONE       2010/06/12 15:01:00  [EXIT]  insert into t1 values(16,'Skirt')                                                                                                                                                                                                                                                                                                                                                                                                                                           

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

              3868 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:56  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   TABLE   GRANTED   IX  .NONE       2010/06/12 15:01:50  [EXEC]  select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER,TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN !='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1,2,3,4,5,6                                                                                              

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

              3868 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:56  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   PAGE    GRANTED   IU  .NONE       2010/06/12 15:01:50  [EXEC]  select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER,TABLE_NAME, LK_NEW_MODE, LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSTEM.SYSLOCK, SYSTEM.SYSTABLE, SYSTEM.SYSUSER where HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and LK_GRAN !='SYSTEM' and TABLE_NAME not like 'SYS%' order by 1,2,3,4,5,6                                                                                               

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

We found connection ID 1976 lock the resource by executing command: insert into t1 values (16,'Skirt').

Next, execute following SQL statement to get more detailed information for connection 1976 and Object 2.18.12.

dmSQL> select HOLD_LK_CONNECTION, LOGIN_IP_ADDR, LOGIN_HOST, LOGIN_TIME, USER_NAME, TABLE_OWNER, TABLE_NAME, LK_NEW_MODE,

LK_GRAN, LK_STATUS, LK_CURRENT_MODE, TIME_OF_SQL_CMD, SQL_CMD from SYSLOCK, SYSTABLE, SYSTEM.SYSUSER where

HOLD_LK_CONNECTION=CONNECTION_ID and TABLE_ID=TABLE_OID and HOLD_LK_CONNECTION=1976 and LK_OBJECT_ID='2.18.12' order by

1, 2, 3, 4, 5, 6;

 

HOLD_LK_CONNECTION  LOGIN_IP_ADDR                  LOGIN_HOST                     LOGIN_TIME                                                                 USER_NAME                                                                                                                       TABLE_OWNER                                                                                                                       TABLE_NAME                                                            LK_NEW_MODE LK_GRAN LK_STATUS LK_CURRENT_MODE   TIME_OF_SQL_CMD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

                                        SQL_CMD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

=======================================================================

              1976 127.0.0.1        LA-WANGXUEWEI                            2010/06/12 14:59:52  SYSADM                                                                                                                           SYSADM                                                                                                                           T1                                                                                                                               NONE.NONE   TUPLE   GRANTED   X   .NONE       2010/06/12 15:01:00  [EXIT]  insert into t1 values(16,'Skirt')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

This example to illustrate, the state of connection 1976 is [EXIT] insert into t1 values (16,'Skirt'), it denotes SQL command had been executed completely but doesn’t release resource. Reason may be summarized as two: one is the program hasn’t committed, the other is connection broken, this caused subsequent commit commands execute failed and make database server to a waiting status. Because of above reasons resource 2.18.12 can’t release all the while. You can execute kill connectioncommand to force resource release.

Overall, you can obtain the information from above two ways and start to analyze the reasons for Lock time-out, you should know the connection status which occupies the resource and busy or idle. If the behavior is normal, it will be released the resource after execution completely. And if the connection status is idle, you should execute “kill connection HOLD_LK_CONNECTION number” and release the resource.

 

2) The solution when LOCK TIME-OUT occurs

In order to improve the concurrency degree for system and reduce the waiting time for each transaction can adopt the following methods:

The first one way, shorten the transaction length and let each transaction accomplish in shortest time as soon as possible.

Second, reduce lock granularity and use tuple lock as possible. (In DBMaker, the default lock granularity level is row)

Third, use index san.Multiple transactions scan a same table with different indexes maybe improve the concurrency degree.

The last one, reduce the isolation level for transaction on condition that no affect for the validity of transaction. (DBMaker default lowest level is: read un-committed)

Version: DBMaker 5.x

Product: Normal, Bundle

Platform: Windows

Author: Sandy
Last update: 2016-03-09 10:24


How to connect to Server from Client in PHP program with DBMaker? (DBMR2016)

For PHP programs connect to DB, we usually just need to set ODBC data source, then connect through DSN name. For example: $Conn =odbc_connect (“DBSample5”,”SYSADM”,”xxxxx”);

If you need to let program Client and DB Server are separated, we can do following:

1. Install DBMaker Driver or Client in Client.

2. Set IP and PortNum for DB

3. Add DSN in ODBC data source.

Note: Because dmconfig.ini had been set, you only need to choose DB Name from DropDownList (Database name (N) :) options in ODBC setting.

Version: DBMaker 5.x

Product: Normal, Bundle

Platform: Windows

Author: Sandy
Last update: 2016-03-09 10:25


How to upgrade License for DBMaker?

Upgrade license can increase the number of concurrent users when you purchased DBMaker.You can upgrade license after you received your upgrade license serial number. Follow the directions below.

Windows platforms

    1. Select Programs from the Windows Start menu, select the DBMaker program group, and then choose Upgrade. The upgrade program will start.

    2. Click the License button to show the current license information. Click OK to return to                   upgrade program.

    3. Click the Next button. The CASEMaker End-User License Agreement will appear.

    4. Read the End-User License Agreement carefully and click I Agree if you agree to abide by the terms and conditions of the agreement. The CASEMaker Product Activation Introduction will appear.

    5. Click the Next button. The Product Activation will appear.

    6. Enter the upgrade serial number and proxy information in the appropriate locations, and click Next. The Activation Information dialog box with Installation ID will appear.

    7. Click OK if the upgrade program can access the internet. If the activation process correctly, it will show "Your product is now activated". If the activation process fails, please save the Installation ID and then follow the instruction on the Activation Information dialog box to manually activate the product.

    8. Click OK button.  The Support Information dialog box will appear. Contact information for CASEMaker Technical Support will be displayed.

    9. To exit, click Finish. Use the new license number the next time you start DBMaker.

Linux platforms

    1. Change your working directory to the /APP_HOME/bin directory. This directory contains the upgrade program you will use to upgrade your current license serial number.

    2. Execute the upgrade program. The upgrade program will start and present you with several options.

    3. Select the second option: Product Activation. The CASEMaker End User License Agreement appears.

    4. Read the End User License Agreement carefully. Continue to the next step if you agree to abide by the terms and conditions of the agreement.

    5. Type the upgrade serial number and proxy information when prompted. Type 'A' and press Enter if your computer can access the network. Or type 'M' and press Enter to see the Installation ID and the instruction about how to manually activate the product.

Networks disconnect:

You can upgrade license via following directions if your internet access is unavailable or the activate option fails.

Please save the installation ID first, then copy to http://activate.dbmaker.com.tw/ using a computer with Internet access and click Submit button. You will receive an activation.dat file. Copy this file into C:\DBMaker\5.x\ on the original computer to complete DBMaker activation.

Version: DBMaker 4.x, 5.x

Product: Normal, Bundle

Platform: Windows, Linux

Author: Sandy
Last update: 2016-03-09 10:26


How to avoid “ERROR (6101): allocated too many command handles”(DBMR: 2017)?

Applications can allocate 257 statement handles in one connection by default. The error will be return when statements reached database limited value.bescause Create Statement but not corresponding Close Statement in application will cause statement increasing and achieve the limiting value. So if errors occur you should check the application whether corresponding Close Statement exist. In addition, if because of business or other reasons to be statement over than 257, you can set keyword DB_MXCMD= xxx in dbmconfig.ini file to solve this problem. Attention please, you need restart DB server after set DB_MXCMD keyword.

Version: DBMaker 5.x

Product: Normal, Bundle

Platform: Windows, Linux

Author: Sandy
Last update: 2016-03-09 10:44


How to avoid connect error from client to server when database started by dmservice?

Install DBMaker Server and Client in different computers. Start db by dmservice in Server and connect to db from Client. If connect failed, you can turn off firewall first and try again, the problem may be solved. Of cause, this is only possible reason not the only reason. If the error can’t solve after you turn off firewall you can found the reasons from other aspects.

Version: DBMaker 5.x, DBMaker 5.x

Product: Normal

Platform: window XP

Author: Sandy
Last update: 2016-03-09 10:47


How to avoid Lock time-out occurs when different applications access the same table in database with different operations simultaneously? For example, unload and other DML operations occur simultaneously. (DBMR2123)

Locks are required for concurrency control between multiple transactions on the same database objects. Lock time-out will be occurring when multiple users access to one database objects simultaneously. Although it’s normal behavior, in order to ensure the consistency of the data in database. However, we still need the simultaneous access requirements can be met in some special conditions. But how to handle the specific requirements? Differnt needs have different handle methods mainly according to practical problems.

Below we make DBMR2123 as an example to elaborate:

When customer unloads data from table test, another application programs operate the same table test (delete, insert etc) at the meantime, Lock time-out will be occur. In order to avoid Lock time-out occur we can use the temporary table as a workaround.

Detailed operation as follows

Select * from test into temp_test;

Unload data from (select * from temp_test) to test_file;

It doesn’t influence the original table performance but need extend the deftablespace because temporary table regular specified on deftablespace currently.

 

Version: DBMaker 4.x\5.x DBMaker4.x\5.x

Product: Normal

Platform: Windows xp

Author: Sandy
Last update: 2016-03-09 10:49


How to avoid line wrap if the column data contains return code after execute EXPORT command and output to CSV data file? (DBMR: 2126)?

There is incorrect handling result output to CSV file if the column data which contains return code when execute “Export command”; i.e Table has only one record but more than one records judged by DBMaker if the record include return code. We can get expected output result by setting the parameters in description file.

For example:

This is the previous description file.

FORMAT=VARIABLE

COLUMN_DELIMITER=','

ROW_TERMINATOR="\n"

QUOTATION=SINGLE_QUOTE

NUMBER_OF_ROWS_FOR_EACH_TRANSACTION=-1

After execute “Export into testtb.csv table testtb description desc.txt;” command with above description file, Incorrect line wrap displayed in CSV file.

We can modify the original contents as following in description file to ensure output result correct and do not automatically new lines.

The modified description file.

FORMAT=VARIABLE

COLUMN_DELIMITER=','

ROW_TERMINATOR="\r\n"

QUOTATION=DOUBLE_QUOTE

NUMBER_OF_ROWS_FOR_EACH_TRANSACTION=-1

Version: DBMaker5.x, DBMaker4.2, DBMaker5.x

Product: Normal

Platform: Window xp

Author: Sandy
Last update: 2016-03-09 11:05


2011

How to get indexed column name of table from SQL by VB6.0 OR 2005 with ADODB connection? (DBMR2040)

The SQL cannot get indexed column name directly, it should parse multi-SQL to get. First, get num_column, reserve3 from system.sysindex, second, from above result then match in column_name of system.syscolumn.we can write a store procedure by ec to realize the above process.

We can get database metadata of indexed column by the ADO function of openSchema via SQLStatistics.

It should be noticed that OleDB of DBMaker5.1 can support this method. For DBMaker4.3 version, you should modify the Provider value to “MSDASQL” and data source value to “dbsample4”;

Usage as following:

DBMaker4.x

Dim conStr As String

Set conn = New ADODB.Connection

conStr = "Provider=MSDASQL; data source=dbsample4; user id=sysadm; password=;  "

 conn.Open conStr

 'Set rs = conn.OpenSchema(adSchemaIndexes, Array(Empty, "scheme_name", Empty, Empty, "table_name"))

While Not rs.EOF

 Debug.Print rs!COLUMN_NAME

   rs.MoveNext

 Wend

DBMaker5.x

Dim conStr As String

 Set conn = New ADODB.Connection

conStr = "Provider=dmole51; data source=dbsample5; user id=sysadm; password=; "

 conn.Open conStr

Set rs = conn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "CARD"))

 While Not rs.EOF

 Debug.Print rs!COLUMN_NAME

 rs.MoveNext

 Wend

Version: DBMaker 5.x, DBMaker 4.3

Product: Normal

Platform: all windows

Author: Sandy
Last update: 2019-04-09 08:37


How to avoid error: 10851 - invalid value in configuration file when database creation on 5 series reference old 4 series configuration setting on DB_USRBB? (DBMR: 2106)?

 When customer upgrade database from 4 series to 5 series, the customer should create an empty 5 database firstly. If customer copy same database setting from 4 as 5 database setting, they possible face database creation error – 10851 (invalid value in configuration file) for DB_USRBB setting value. Because the DB_USRBB default value is re-defined on 5 series, there are two ways to avoid this error.

 

[4.x db]

Db_usrbb=/home/dbmaker/4.3/database/customer.bb 2

[5.x db]

Db_usrbb=/home/dbmaker/5.2/database/customer.bb 3

1) Modify 2 to 3 for *.BB size.

2) Removed the parameter directly from the dmconfig.ini file.

Version: DBMaker4.x, DBMaker5.x

Product: Normal / Bundle

Platform: All

Author: Sandy
Last update: 2016-03-09 11:49


How to avoid “Failed to start Service for DBMaker with 11206 Error in restarting OS” (DBMR: 2111)?

We want to use Windows Service to start DB but the DB can’t start automatically when restart OS and ‘11206’ - cannot create/open a file error will be return at the meantime. The unexpected error occurs because of the file privilege owned by other process at that time, not only journal file occur. When database server try to get all db access privilege, if any file had owned by other process like anti-virus software (nod32).  db server will report this error. We can through check and adjust anti-virus software option to let db server regain all db files access privilege to avoid this error.

Version: DBMaker4 series / 5 series

Product: bundle

Platform: All windows

Author: Sandy
Last update: 2016-03-09 11:51


How about page compress operation internally? (DBMR: 2081, DBMR: 2132)?

Page compress will lock the whole page forcedly and escalate lock to page if lock granularity less than page lock, but higher lock granularity have higher opportunity cause deadlock occur on higher concurrency .So we should try to reduce page compress operation internally though some useful ways.

Database compress page will be occur more easily on these two conditions.One is table include VARCHAR data type ,the other is table can include any other data type but without initial size and use DEFAULT NULL .

For VARCHAR,it’s varchar special behavior, varchar type fit real data size not allocate space fix always. So update data in varchar type cause original size is not enough for updated data easily, need to find other continuous space. When free space is enough in page but not continuous; DBMaker Server will compress the page for collect fragments space automatically. Database compress page lock whole page forcedly, it's DBMaker try to use free page in the page,at this moment, the real lock causing from compress operation.

For other data type which supported by DBMaker,its initial value was defined “DEFAULT NULL” at original,so not storage space preset in table initializtion.when putting new row data into it,DBMaker should check continuous free space in page.The situation like above behaviors.

In order to reduce Page compress we can reference the following Suggestions:

  1. Replace varchar to char data type and give data initial value like other data type use NOT NULL.
  2. Adjust Fillfactor value to an appropriate value.
  3. Both 1 and 2 only reduce the opportunity for page compress but not avoid thoroughly.If method 1and 2 not enough needs we will provide other way depends on customers’s request to solve this problem.

Version: DBMaker4.x, DBMaker5.x

Product: Normal / Bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 02:59


How to get the DEADLOCK error message (DBMR: 2081)?

In order to get more useful information for DEADLOCK we can set some keywords in dmconfig.ini file to trace. Detailed setting for NEW log system for DEADLOCK investigation will be list as follows:

You can set follow keywords in dmconfig.ini

DB_LGDIR=<you log path>

DB_LGLCK=1

DB_LGSVR=3 ;( 1......6)

If you want to get more information you can set keyword DB_LGSVR greater than 3 .Finally, you can check the error message about deadlock in directory which specified by DB_LGDIR When dead lock occur.

Version: DBMaker4 series / 5 series

Product: Normal/ bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 03:05


How to start/ stop dmserver and how to check dmserver status in redhat linux?

we can put DBSAMPLE5 as a sample in this case.

1. The methods for start and stop dmserver in Linux as follows:

1)       startup dmserver:

/home/DBMaker/5.2/bin/dmserver DBSAMPLE5

2)       stop dmserver (Use client tool dmsqlc )

dmsqlc>connect to DBSAMPLE5 SYSADM;

dmsqlc>terminate db;

dmsqlc>quit;

Note: Customer also can use command to shutdown db but this not recommends way.

/home/DBMaker/5.2/bin/dmserver –k DBSAMPLE5

2. The methods for check dmserver status in Linux description as follows:

Customer can use ps command ” ps -ef | grep dmserver” to check dmserver process whether exist and still need to check shared memory and clean shared memory .we suggests user restart operating system to solve this problem.

Version: DBMaker4 series / 5 series

Product: Normal / Bundle

Platform: All Linux

Author: Sandy
Last update: 2016-03-10 03:40


How to use 32bit Application with DBMaker on 64bit sever? (DBMR: 2106, 2144)

 If user has a 32-bit Program and they would like to install it on a 64-bit Windows server along with DBMaker, since the program is 32-bit and user is not willing to rebuild the program as 64bit or the program can not convert to 64bit, it requires a 32-bit data source, but the OS is 64bit. In this case, if we try to install the DBMaker Driver using the normal setup will failed and face error. But its normal behavior for this error because of DBMaker 32bit Normal version setup can’t work on x64 Environment. However, DBMaker 32bit bundle version can support in x64 OS and access database server via 32bit odbc driver manager. So we can suggest the user to use our 32-bit bundle version to avoid this error occur.

If user’s AP can be compiled to 64bit and they are willing to use the 64bit AP, and they used DBMaker5.1 or above version, they don't need to use our 32-bit bundle on 64bit OS, they can install 64bit normal version directly. Because DBMaker have began to support 64 bit OS from 5.1 version.

The next, we will introduce how to register 32bit bundle driver and DSN on 64bit OS, the detailed steps as below.

(1)Register 32bit bundle driver  

Steps (1) create a new key in the [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers] position:

DBMakerName =”Your Driver Name”, for example, you can named” DBMaker 5.1B Driver”

Type=”REG_SZ”

Data=” Installed”

As follows:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers]

"DBMaker 5.1B Driver"="Installed"

Step (2) Register “Your Driver” in this position:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI]

The ordinary method is create and edit a .reg file (e.g.: DBMaker 5.1B Driver.reg), then import the register-file into the registry. The .reg file’s content as below:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\DBMaker 5.1B Driver] "Driver"="D:\\bundle\\DMAPI51.DLL"

"Setup"="D:\\bundle\\DMSET.DLL"

"APILevel"="1"

"ConnectFunction"="YYN"

"DriverODBCVer"="03.00"

"FileUsage"="0"

"SQLLevel"="1"

"HOME"="D:\\bundle"

Note: The 32bit ODBC Driver must register to \ Wow6432Node\....

(2) Register 32 bit DSN    

In general, there are two kinds of methods to create 32bit DSN on 64bit OS.

The one is, if dmset.dll exist in you system OS, you can launch odbcad32.exe directly from C:\Windows\SysWOW64\ directory to create DSN and then check the connection.

The other is edit registry directly. Create a .reg file and adds following contents, then import the register-file into the registry.

 [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]

"DBSAMPLE5"="DBMaker 5.1B Driver"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\DBSAMPLE5]

"Driver"="D:\\DBMaker\\5.2\\bin\\DMAPI52.DLL"

"Database"="DBSAMPLE5"

Note: The first “DBSAMPLE5” stand for DSN name and the second “DBSAMPLE5” stand for your DataBase Name, you can give the same or different name for them depend on yourself.

Version: DBMaker All

Product: Bundle

Platform: All Windows

Author: Sandy
Last update: 2016-03-10 07:07


How to use COPYTABLE to duplicate a new table from old ones? (DBMR: 2136)

We can use call stored procedure “CALL COPYTABLE ()” to duplicate a new table based on an existing table. The usage as below:

COPYTABLE(VARCHAR(32)   source_schema_name INPUT,

            VARCHAR(32)   source_table_name INPUT,

            VARCHAR(32)   destination_schema_name INPUT,

            VARCHAR(32)   destination_table_name INPUT,

            VARCHAR(128)  tablespace_lock_mode_option_string INPUT,

            VARCHAR(2048) where_condition_string INPUT,

            INT           fg_rename_index INPUT,

            INT           commit_count INPUT)

    Copy the source table's definition and data into the destination table.

    Need to pay attention to the following comments when use this stored produce:

    (1). It must be run when autocommit is on.

    (2). User can set 0 or 1 for rename index according DCI rule flag.

    (3). Set commit count to commit when copying every nth data. User can set 0 to n.

    NOTE: When any error occurs, any operation before the error occurred will be

          committed. The command that did not execute for copy table will be showed

          in the _spusr.log. User can set DB_SPLOG directory in the dmconfig.ini

          or find it in the directory where user executes the application.

The 5th argument needs to pay attention in use process. The 5th argument allows customer set 3 options: Tablespace, Lockmode and Fillfactor. In addition, you can set one of the arguments one time and you also can set 3 options in 5th argument at the same time. Please notice if you specify in same tablespace, all setting will be followed source table, if you specified another tablespace, please remember set different options - lock mode or fillfactor for different value, otherwise, it will take default in different tablespace.

For example:

(1)Create a table named t1 and def the t1 schema.

dmSQL> def table t1;

create table SYSADM.T1 (

 C1  TIMESTAMP default null )

 in DEFTABLESPACE  lock mode row  fillfactor 100 ;

(2) Specify t2 in same tablespace with T1, then def t2.

dmSQL> call copytable('SYSADM', 'T1', 'SYSADM', 'T2', '', '', 0, 1);

 

dmSQL> DEF TABLE T2;

create table SYSADM.T2 (

 C1  TIMESTAMP default null )

 in DEFTABLESPACE  lock mode row  fillfactor 100 ;

(3) Specified another tablespace for t2 which different with t1, then def t2.

dmSQL> call copytable('SYSADM', 'T1', 'SYSADM', 'T2', 'IN TS1 LOCK

MODE PAGE FILLFACTOR 75', '', 0, 1);

//tablespace : ts1

//lock mode : page

//fillfactor : 75

 

dmSQL> def table t2;

Create table SYSADM.T2 (

 C1 TIMESTAMP default null)

 in TS1  lock mode page  fillfactor 75 ;

General speaking, lock mode must as row is better.

Version: DBMaker 4.3 or Above

Product: Normal / Bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 08:45


How to avoid the error: 18065 that cannot read codepage file?

We can make sure the Client Driver can read the codepage (Bundle\shared\codepage\xxxx.map) first, and can duplicate the error after dropping all the .map files.

For example: Somebody drop the .map files by accident, or cannot access them for authority or something wrong.

Version: DBMaker 5.x, DBMaker 4.x

Product: Normal / Bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 08:48


How to avoid the problem that user have installed and activated DBMaker in a Windows VM which is in evaluation, after activating Windows VM, DBMaker didn't start anymore and reports the “invalid license” error?

We installed a licensed copy of DBMaker 5.2 in a Windows VM during Windows VM's evaluation period. After activating Windows VM, DBMaker reports that the license is invalid. Its normal behavior of the error because of generating the activation.dat file need according to Machine ID which related with product license of Windows VM OS. So after activating the Windows VM, DBMaker didn't start anymore and reports the “invalid license” error .To solve the problem we need reinstalling the DBMaker license using “Upgrade”.

Version: DBMaker5.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-10 08:53


How to avoid error 5388 when user use DBMaker Bundle version?

If we have a COBOL program and want to access DBMaker (bundle version) with DCI in Linux, we need to build the runcbl runtime first, and build runtime need to link libdmapic.so file.DBMaker provide different link file name for Normal version and Bundle version, For normal, the corresponding file is libdmapic.a.we must ensure use the correct file name when build runtime. If user builds runcbl runtime with libdmapic.a, but the DBMaker Linux bundle version does not include the libdmapic.a file. When connect to db, it will return the error of 5388(The characters convert tools open failed). Please rebuild runcbl runtime with libdmapic.so file and please also remember to modify libdmapic.a to libdmapic.so in MAKEFILE that can avoid this error, In addition, please set LD_LIBRARY_PATH environment variable to xxx/bundle, for example: export LD_LIBRARY_PATH=/home/bundle.

Version: DBMaker 5.x, 4.x

Product: Bundle

Platform: Linux

Author: Sandy
Last update: 2016-03-10 08:54


How to avoid WARNING63 while migrating db from DBMaker4.1 to 5.2?

User wants to migrate db from 4.1 to 5.2 and they unload DB from DBMaker4.1, and then try to load db to 5.2, but Warning63 (Data truncated when converting from different type) was faced. The Warning63 caused by the last “line break” which is not a printable SJIS symbol. For unloading data from Table, the best workaround should be “set unload chartohex on”, but the feature only have been supported from DBMaker4.3 version.

Generally, we think if drop the last "\t” for the problematical records, the WARNING (63) will be disappeared, but actually, the last "\t" still be truncated and other "\t" still cannot be loaded correctly. And some SJIS Kanji cannot be loaded correctly.

For example: "能"

The hex for "能" is 945c, the "5c" is same with hex of line break - "0D0A", so "5c" is taken as line break - "\" for mistake. When we load this SJIS Kanji the "945c" is truncated to "94", which cause some wrong record data, not same as before.

In addition, the DBMaker4.1 version does not support the workaround “set unload chartohex on”. So we can’t use unload/load directly from 4.1 to 5.2, the right way as below:

(1). Upgrade DBMaker4.1 to DBMaker4.34. We can start db with DB file of DBMaker4.1,, and please backup DB at first.

(2). Unload data from DBMaker4.34 with "set unload chartohex on".

(3). Load data to DBMaker5.2.

Then we can successfully unload db from DBMaker4.1 version and load db to DBMaker5.2 version.

Version: DBMaker5.x

Product: Normal /Bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 08:56


2012

How to avoid the use of high-frequency characters 3?

First, user should backup the file tw.tab which located in c:\dbmaker\4.3\shared\stopword\ tw.tab, then open tw.tab and delete following contents.

0

1

2

3

4

5

6

7

8

9

10

After that, rename tw.tab to stopword.tab.(xx.tab user can named it by themselves)

Second, set DB_STPWD= stopword.tab in dmconfig.ini, then restart database.

At last, delete the original text index and recreated it can exclude high-frequency characters 3.

 

Version: DBMaker5.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-10 09:01


How to make the MFCOBOL Compatible with Visual COBOL?

We found some errors (such as “Not support N type in Visual Cobol “,” 114 Error”) will be faced when compile COBOL PROGRAM after replace Micro Focus Net Express to Visual COBOL,” so following 4 points need to pay attention when compile COBOL PROGRAM with Visual COBOL.

  • Environment Variables

On 32 bit Windows

set path=%path%;C:\Program Files\Micro Focus\Visual COBOL 2010\bin

On 64 bit Windows

set path=%path%;C:\Program Files (x86)\Micro Focus\Visual COBOL 2010\bin64

  • Microsoft Visual Studio

In Net Express 5.1, VC version is 9.0, and for Visual COBOL 2010R4, the VC version is 10.0. So we must install the right version and set the right PATH to make them work. For example:

On 32 bit Windows

call "C:\Program Files\Microsoft Visual Studio 10.0"\VC\bin\vcvars32.bat

copy "C:\Program Files\Microsoft Visual Studio 10.0"\VC\lib\oldnames.lib .

On 64 bit Windows

call "C:\Program Files (x86)\Microsoft Visual Studio 10.0"\VC\bin\amd64\vcvars64.bat

Copy "C:\Program Files (x86)\Microsoft Visual Studio 10.0"\VC\lib\amd64\oldnames.lib .

  • Definement for N type

03 WK-N PIC N(50).

03 WK-5 PIC N(01).

In Net Express 5.1, the N type is defined with 152, in Visual Cobol 2010R4, it’s defined with 168. We must add a new type in MFDCI to map it. So only the new version DCI can support N type for Visual Cobol.

  • FCD3 option for x32 platform

The File Control Description (FCD) is a data area which contains information about the file in use. There are two versions of the FCD, and which one is used depends on whether your COBOL development environment is running in 32-bit or 64-bit, as shown in the following table:

COBOL Development System

FCD Used

Mainframe Express

FCD2

32-bit Visual COBOL

FCD2

64-bit Visual COBOL

FCD3

.NET Support within Visual COBOL

FCD3

32-bit Server Express

FCD2 or FCD3

64-bit Server Express

FCD3

 

Because "FCD3" is the default options for Visual Cobol on 32bit/64bit which not same as Net express (use "FCD2" for 32bit and “FCD3 for 64bit). We need compiling with “NOFCD3” option for Visual Cobol on 32 bit OS, otherwise the 114 Error will be faced. Usage as following:

First, add following information in xfd.cfg (User can specify the path for xfd.cfg) file:

NOFCD3 CREATEXFD CALLFH “DBMAKETINTF”

Then execute cbllink command as below:

cbllink -oTEST.exe -uxfd.cfg -g -b TEST.cbl oldnames.lib dmmfcbl.lib dmdcic.lib dmapi52.lib

or

cbllink -oTEST.exe -u C:\test\xfd.cfg -g -b TEST.cbl oldnames.lib dmmfcbl.lib dmdcic.lib dmapi52.lib

In addition, we will provide a new DCI lib which uses the NOFCD3 as default option and user don’t need add any options.

cbllink -oTEST.exe -uxfd.cfg -g -b TEST.cbl oldnames.lib dmmfcbl_fcd3.lib dmdcic.lib dmapi52.lib

Note 1:

The new lib is only for 32bit platform and it’s not compatible with Net Express version (totally different data structure), so we rename the lib name to dmmfcbl_fcd3.lib.

Note 2:

If users want to build the COBOL programs to INT or GNT format, because COBOL command cannot identify the NOFCD3 option, please use new lib dmmfcbl_fcd3.lib to replace dmmfcbl.lib to build runtime to avoid the 114 Error (memory access violation). Usage as following:

Build the DBMAKERINTF.dll

cbllink -v -K -L -d -oDBMAKERINTF.dll mfcall.obj oldnames.lib dmmfcbl_fcd3.lib dmdcic.lib dmapi52.lib

Build the GNT and execute it with DBMAKERINTF.dll.

cobol test.cbl CREATEXFD CALLFH "DBMAKERINTF.dll" gnt

 

Version: DBMaker5.x

Product: Normal /Bundle

Platform: All

Author: Sandy
Last update: 2016-03-10 09:45


How does make the collating order file as word sort order for Japanese? (dbmr2178)

About collating order, DBMaker work with default binary sort order.

if user want to use other Language as an option, should create an order file which similar with big5_stroke.ord, and place in same C:\DBMaker\5.2\shared\codeorder. Then set DB_Order = xxxx_stroke.ord in dmconfig.ini

We had provided an order file sjis_dict.ord for SJIS.

Version: DBMaker 5.x, 4.3

 

Product: Bundle/Normal

Platform: ALL

Author: Sandy
Last update: 2016-03-10 11:11


How to avoid the limitation when the situation multi-thread uses same connection handle to call server function (DBMR2299)?

Some unexpected behaviors (such as: hung-up) will be faced when multi-thread use same connection handle to call server function. we can set DB_MTIMO to solve the problem.

DB_MTIMO = 0/n (off/ seconds)

DB_MTIMO used to ensure only one odbc function will be executed for one connection in the same time. Use latch to prevent another thread execute the odbc function with the same connection handle in the same time.

 

Although we can set this keyword to solve the multi-thread problem but we still don’t recommend that user setting it in many usual situations, because it may cause performance down or some unknown exceptions.

 

Version: DBMaker5.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 03:04


What about the null convert functions in DBMaster(DBMR2355)?

Following are NULL convert function in DBMaster, please reference.

 

1. IFNULL is an ODBC function. IFNULL (v1, v2) is the equivalent to coalesce(v1,v2) and it’s equivalent to “if v1 is not null, then v1 else v2”.

2. NULLIF is an SQL 99 function. NULLIF(v1, v2) is the equivalent to “if v1=v2 then NULL else v1.

3. COALESCE is an SQL 99 function. COALESCE (v1, v2, v3,….vn) is equivalent to “if v1 IS NOT NULL then v1 else if v2 IS NOT NULL then v3 else………….else vn”. 

 

Version: DBMaker4.x/5.x

Product: Normal /Bundle

Platform: All

Author: Sandy
Last update: 2016-03-18 03:06


How to avoid the double reading problems when read data from Long Varchar type by ODBC in DBMaker5.2?

Insert a large quantity of pure text data into a column which is Long Varchar type, and then read the contents with JDBATool, the query results are correct. But if we read it with ASP.NET, double contends will be readout. Part of the sample AP code as below.

string strSQL = @"SELECT LNSOURCE FROM TMP_FLNAME WHERE PUBLISH = 'N0'

AND FLCODE = ?;";

OdbcConnection Conn = new OdbcConnection(strConnLaw);

Conn.Open();

OdbcCommand Comm = new OdbcCommand(strSQL, Conn);

Comm.Parameters.Add("FLCODE", OdbcType.Char, 8).Value = hidFLCode.Value.Trim();

OdbcDataReader dr = Comm.ExecuteReader();

if (dr.Read())

{

        // The datatype of LNSOURCE is long varchar

        Response.Write("<pre>" + dr["LNSOURCE"].ToString()+"</pre>");

}

dr.Close();

Conn.Close();

 

We can use OleDb to avoid the double reading problem, but please note that user need to confirm the “Provide” should be”Provider=DMOLE52” but ”Provider=DMOLE52.1”in connection string.

In addition, if users insist on using ODBC, they can avoid the double reading problem by modifying the AP, can be read data by using “GetChars”and add ”CommandBehavior.SequentialAccess”in DataReader.

OdbcDataReader dr = Comm.ExecuteReader(CommandBehavior.SequentialAccess);

  if (dr.Read())

            {

                char[] buffer;

                int iStart = 0;

                long returnLen;

                buffer = new char[256];

                returnLen = dr.GetChars(0, iStart, buffer, 0, 256);

                richTextBox1.Text += new string(buffer);

                while (returnLen > 0)

                {

                    iStart += (int)returnLen;

                    returnLen = dr.GetChars(0, iStart, buffer, 0, 256);

                    richTextBox1.Text += new string(buffer);

                }

            }

            dr.Close();

            Conn.Close();

 

Version: DBMaker 5.x, 4.3

Product: Bundle/Normal

Platform: ALL

Author: Sandy
Last update: 2016-03-18 03:15


Creating IVF text indexes occupies a large amount of memory which comes from OS, not DCCA? (DBMR2242)

Creating inverted-file text indexes requires a large amount of memory resource. The memory extra allocated for inverted-file text indexes by OS doesn’t occupy the shared memory – DCCA and it will be free after use. Users can manually specify the approximate upper bound of the memory by adding the keyword DB_IFMem in dmconfig.ini. If users have no plan to use IVF text indexes, there is no need to set the keyword DB_IFMem.

 

Version: DBMaker4.x/5.x

Product: Bundle/Normal

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:16


How to allocate pages for tables or indexes in DBMaker? (DBMR2238)

When a table is created, the number of pages allocated for it depends on the number of columns, the column type and table header information in this table. However, if users only create an empty table and no record is inserted, only the table’s related information is stored into SYSTABLE without allocating data pages for this empty table. When an index is created, the number of pages allocated for it depends on how many columns is based on to create it and its complexity. However, if users only create an empty index and no record is inserted, only the index’s related information is stored into SYSINDEX without allocating index pages for this empty index. In one word, users can’t define the number of pages allocated for tables or indexes by themselves.

The system table schema of DDL is stored in different system table respectively. For example:

Create table t1 (c1 int, c2 int);

Create index idx1 on t1 (c1);

One record will be added into SYSTABLE in P1 to record t1’s related information.

Two records will be added into SYSCOLUMN in P2 to record related information of c1 and c2.

One record will be added into SYSINDEX in P3 to record idx1’s related information.

Now, if users continue to create table named t2 with the command “create table t2 (c3 int, c4 char(10), …)”, the record used to record t2’s related information will be added into SYSTABLE in P1 if there is enough space in P1, otherwise, a new page named P4 will be allocated for SYSTABLE and the record will be added into P4. Meanwhile, the two records used to record related information of C3 and C4 respectively will be added into SYSCOLUMN in P2 if there is enough space in P2, otherwise, a new page named P5 will be allocated for SYSCOLUMN and the two records will be added into P5.

 

Version: DBMaker4.x/5.x

Product: Bundle/Normal

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:18


How to backup file objects? Can users restore a database without backing up file objects? (DBMR2241)

Users can specify the file object (FO) backup mode by setting the keyword DB_BkFom for a full backup.

Ÿ   DB_BkFoM = 0: Don’t back up file objects

Ÿ   DB_BkFoM = 1: Back up system file objects only

Ÿ   DB_BkFoM = 2: Back up both system file objects and user file objects

When a full backup is performed, an FO directory is created under the backup directory. If the value of DB_BkFom equals 1, only system file objects are copied into the FO directory; if the value of DB_BkFom equals 2, both system file objects and user file objects are copied into the FO directory. The backed-up file objects are renamed sequentially when they are copied into the FO directory. For example, if the name of the original external file are “ZZ000001.bmp,... , ZZ00AB32.txt”, the backup server would copy them into the FO subdirectory and rename them “fo0000000000.bak,... , fo0000002345.bak”', meaning they are the 1st file object and the 2345th file object. The mapping between the source full file name and its new name is recorded in the file object mapping list file, dmFoMap.his. It is a pure ASCII text file and necessary for restoring file objects. The format is as follows.

Database Name: DBSAMPLE5

Begin Backup FO Time: 2012/01/29 09:21:32

FO Backup Directory: C:\DBMaker\5.2\SAMPLES\DATABASE\backup\FO\

[Mapping List]

s, fo0000000000.bak, "C:\DBMaker\5.2\SAMPLES\DATABASE\backup\FO\ZZ000001.bmp"

u, fo0000000001.bak, "C:\DBMaker\5.2\SAMPLES\DATABASE\backup\FO\image.jpg"

....

s, fo0000002345.bak, "C:\DBMaker\5.2\SAMPLES\DATABASE\backup\FO\ZZ00AB32.txt"

If the value of DB_BkFom equals 0, the database also can be restored without backing up file objects. However, users should notice that when restore the database to another new database, if the value of DB_BkFom equals 0, users need to manually copy both system file objects and user file objects into the FO directory, and if the value of DB_BkFom equals 1, users only need to manually copy the user file objects into the FO directory. Users can refer the listed file in dmFoMap.his to ensure all related directory exist.

 

Version: DBMaker 4.x/5.x

Product: Bundle/Normal

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:22


2013

How to make C compilers available which are installed after DBMaker’s installing previously? (DBMR2252)

Suppose a user had installed DBMaker without installing C compilers previously and now he want to install C compilers for the installed DBMaker, for parts of DBMaker versions, users need to reinstall DBMaker and choose the type of C compilers during installation, meanwhile for 4.3.x and 5.x.x version built after March of 2009, users don’t need to reinstall DBMaker and just need to modify the key value of COMPILER on registry. The path of COMPILER is “HKEY_LOCAL_MACHINE -> SOFTWARE -> DBMaker -> 4.3.x/5.x.x -> COMPILER”.

For example, if a user’s VC compiler is VC2003, he need to modify key value of COMPILER to VC71. For other settings please reference the following table.

 

COMPILER’s registered key value

Users’ VC Compiler   Version

DBMaker Version

32bit/64 bit

VC60

VC6.0

all versions

32 bit only

VC71

VC2003

all versions

32 bit only

VC80

VC2005

from DBMaker5.0

32 bit only

from DBMaker5.1

32/64 bit

VC90

VC2008

from DBMaker5.1

32/64 bit

VC100

VC2010

from DBMaker5.3

32/64 bit

 

Note:

“32 bit only” means VC6.0 and VC2003 installed under 64 bit OS are invalid for DBMaker.

Users need select “for x64” option when install VC compilers under 64 bit OS.

 

Version: DBMaker4.3.x/5.x.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 03:23


How to send mail to DBA immediately when DMERROR.LOG is updated? (DBMR2257)

Once DMERROR.LOG is updated, DBMaker will immediately send DBA an email of which the content is “Please see the alter message in C:\DBMaker5.2\DMERROR.LOG”. (“C:\DBMaker5.2\DMERROR.LOG” will be replaced with the actual path of DMERROR.LOG for different users) The one thing users need to do is setting DB_ERMRv and EB_ERMSv in dmconfig.ini.

For example:

DB_ERMSV = LAMBS.ad.syscom.com.tw                  ;SMTP server address

DB_ERMRV = Snow_Wang@email.lingan.com.cn        ;DBA’s email aeddresses

 

Version: DBMaker4.x/5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:26


How to start or stop a NT service for a database? (DBMR2260)

Answer2: The command “net start ServiceName” is used to start a NT service and the command “net stop ServiceName” is used to stop a NT service.

For example, suppose a NT service automatically named DBMASTER-DBSAMPLE5 by system for a database named DBSAMPLE5 is created.

The following command is used to start the service.

net start DBMASTER-DBSAMPLE5

The following command is used to stop the service.

net stop DBMASTER-DBSAMPLE5

 

Version: DBMaker 4.x/5.x

Product: Normal/Bundle

Platform: Windows

Author: Sandy
Last update: 2016-03-18 03:28


What’s the function of dmBackup.l_k in DBMaker? (DBMR2268)

dmBackup.l_k is used to lock dmBackup.his. When a backup is performed, DBMaker will automatically create dmBackup.l_k to ensure every process’s write operation on dmBackup.his is mutually-exclusive.

 

Version: DBMaker4.x/5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:29


How to get view definition from database? (DBMR2327)

There are two SQL statements in DBMaker used to get view definition. One is “def view view_name”, and the other is “select cast (view_definition as char(n)) from sysviewdata where view_name='view_name'”.

For example:

dmSQL> def view V2;

Create view SYSADM.V2 as select C1 from SYSADM.T1 ;

 

dmSQL> select cast(view_definition as char(100)) from sysviewdata where view_name='V2';

 

             CAST(VIEW_DEFINITION AS CHAR(100))

============================================================

create view SYSADM.V2 as select C1 from SYSADM.T1

 

1 rows selected

Note: If the value of n is less than length of view definition statement, users can’t get the whole view definition statement. In this instance, users need to set a larger value for n.

Version: DBMaker4.x/5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:32


How to move a table and its indexes into another tablespace with simply commands? (DBMR2363)

From DBMaker5.3, DBMaker supports the command “ALTER TABLE table_name MOVE TABLESPACE tablespace_name” used to move tables and its indexes into another tablespace. Users can’t access the table’s data until the process of moving objects has been finished.

  • ŸIf the table and its indexes are in the same tablespace, users can move them into another tablespace at the same time by using the command. In addition, the index also can be moved into another tablespace that is different the tablespace the table is moved into by rebuilding the index.

For example:

Create table t1 and its index idx1 in tablespace ts1, and then move both of them into tablespace ts2 first by using the command “alter table t1 move tablespace ts2”. Lastly, move index idx1 into tablespace ts3 by rebuilding it. Details are as follows.

dmSQL> create table t1(c1 int,c2 int) in ts1;

dmSQL> create index idx1 on (c1) in ts1;

 

dmSQL> def table t1;

create table SYSADM.T1 (C1 INTEGER default null, C2 INTEGER default null) in TS1 lock mode row fillfactor 100;

create index IDX1 on SYSADM.T1 (C1 asc) in TS1;

 

dmSQL> alter table t1 move tablespace ts2;

 

dmSQL> def table t1;

create table SYSADM.T1 (C1 INTEGER default null , C2 INTEGER default null) in TS2 lock mode row  fillfactor 100 ;

create index IDX1 on SYSADM.T1 ( C1 asc ) in TS2;

 

dmSQL> rebuild index idx1 for t1 in ts3;

 

dmSQL> def table t1;

create table SYSADM.T1 (C1 INTEGER default null , C2 INTEGER default null) in TS2 lock mode row fillfactor 100 ;

create index IDX1 on SYSADM.T1 (C1 asc) in TS3;

  • If the table and its indexes are in different tablespace, users can move the table into another tablespace first, and then move the index into the tablespace by rebuilding the index.

For example:

Create table t3 in tablespace ts1 and create its index idx3 in tablespace ts2. Next, move table t3 into ts3 by using the command “alter table t3 move tablespace ts3”. Lastly, move index idx1 into tablespace ts3 by rebuilding it. Details are as follows.

dmSQL> create table t3(c1 int,c2 int) in ts1;

dmSQL> create index idx3 on t3(c1) in ts2;

 

dmSQL> def table t3;

create table SYSADM.T3 (C1  INTEGER default null , C2 INTEGER default null) in TS1 lock mode row fillfactor 100 ;

create index IDX3 on SYSADM.T3 (C1 asc) in TS2;

 

dmSQL> alter table t3 move tablespace ts3;

 

dmSQL> def table t3;

create table SYSADM.T3 (C1 INTEGER default null , C2 INTEGER default null) in TS3  lock mode row fillfactor 100;

create index IDX3 on SYSADM.T3 (C1 asc) in TS2;

 

dmSQL> rebuild index idx3 for t3 in ts3;

 

dmSQL> def table t3;

create table SYSADM.T3 (C1 INTEGER default null , C2 INTEGER default null)in TS3 lock mode row fillfactor 100;

create index IDX3 on SYSADM.T3 (C1 asc) in TS3;

 

Before DBMaker5.3, users can follow the following steps to move a table and its indexes into another tablespace.

  1. Unload the table to an external text file.

UNLOAD TABLE FROM table_name TO file_name

2. Open the text file “file_name” and manually modify the tablespace name.

alter table SYSADM.TEST primary key (C1) in old_TS1;

==> alter table SYSADM.TEST primary key (C1) in new_TS1;

create index intest_SYSADM.TEST on SYSADM.TEST (C2 asc, C3 asc) in old_TS1

==> create index intest_SYSADM.TEST on SYSADM.TEST (C2 asc, C3 asc) in new_TS1

3. Drop the table “table_name” and then load contents of the table from the text file “file_name”.

LOAD TABLE FROM file_name

 

Version: DBMaker 4.x/5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:44


How to create/rebuild indexes for tables created by other users for SYSADM? (DBMR2372)

To create/rebuild indexes for tables created by other users, SYSADM should add the table owner’s name before the table name rather than the index name.

For example, user staff creates table test_table, and then SYSADM can use the following commands to create/rebuild index test_index for table test_table.

dmSQL> create index test_index on staff.test_table(c2);

dmSQL> rebuild index test_index for staff.test_table;

 

Version: DBMaker4.x/5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 03:49


How to migrate the DBMaker4.3 database from Windows to Linux? (DBMR2384)

For the file system among versions that belong to the same series of DBMaker is compatible, users can only copy data files into Linux and modify the related settings (the dmconfig.ini setting, the keyword path, etc.), and then install DBMaker and directly start the database with the data files.

Users need to pay attention to the following points before migration.

  1. The target Linux platform should support DBMaker.
  2. The hardware on Linux platform should be similar to or higher than that on Windows platform. Additional, the CPU on both Linux platform and Windows platform should be of one type (for example, both are x86 32 bit or x86 64bit), otherwise, users can not directly copy database files and need to migrate them by unloading/loading from Windows to Linux.
  3. It is better to migrate the database from old versions to new versions, e.g. migrate DBMaker4.3.0 to DBMaker4.3.4 or DBMaker4.3.4 to DBMaker4.3.4, rather than DBMaker 4.3.4 to DBMaker 4.3.0.
  4. Modify the dmconfig.ini setting. Additionally, users need to start the database in new journal mode if they adjust the size of journal files.

 

 

Version: DBMaker4.3.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 03:50


How to solve “ERROR (1110): Requested DCCA memory exceeds system maximum” in Linux?

This error occurs in Unix when the DCCA memory requested by the database exceeds the maximum amount of shared memory allocated by the operating system.

To solve this problem, users should set an appropriate value for Kernel.shmmax in the file sysctl.conf located in the directory /etc.

For example:

# controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736

 

Version: DBMaker 4.x/5.x

Product: Normal/Bundle

Platform: Linux

Author: Sandy
Last update: 2016-07-19 09:35


2014

How to ensure the DB can be shutdown normally (which can shorten the starting period next time)? (DBMR2517)

If the database is shut down due to abnormal events of system, it may take much time to restart dmserver. The reason is that, to ensure the consistency of the data, when the database is started next time, DBMaker need to do instance recovery automatically, which costs much time. To solve this problem, users need use Group Policy (The feature of OS) to firstly stop applications and then stop the database before stopping OS, or change the timeout setting of service cease.

If terminating the database with script takes too much time, users can shorten the time by adding a checkpoint in the script before “terminate db;" and execute it.

 

Version: DBMaker5.2.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:49


Which date format is supported by DBMaker? (DBMR2485)

DBMaker supports following date format:

  • Ÿ  Input Format: mm/dd/yy, mm-dd-yy, dd/mon/yy, dd-mon-yy, mm/dd/yyyy, mm-dd-yyyy, yyyy/mm/dd, yyyy-mm-dd, dd/mon/yyyy, dd-mon-yyyy, dd.mm.yyyy.
  • Ÿ  Output Format: mm/dd/yy, mm-dd-yy, dd/mon/yy, dd-mon-yy, mm/dd/yyyy, mm-dd-yyyy, yyyy/mm/dd, yyyy-mm-dd, dd/mon/yyyy, dd-mon-yyyy, dd.mm.yyyy.

The UDF function TO_DATE is used to convert a selected string into any DATE format listed above. Meanwhile, if want to display the date in the format mm/yy, users can use the following statement.

SELECT CAST(MONTH(DT_INI) AS VARCHAR(10)) || '/' || CAST(YEAR(DT_INI) AS VARCHAR(10)) AS "DATE_FORMATED" FROM table_name

 

Version: DBMaker5.2.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:53


How to insert NULL into a field with data type CHAR in DBMaker via DCI in a COBOL program? (DBMR2557)

The following three configuration variables are used to insert NULL into a field with data type CHAR via DCI.

  • Ÿ   DCI_NULL_ON_SPACE_CHAR
  • Ÿ   DCI_NULL_ON_ILLEGAL_DATE
  • Ÿ   DCI_NULL_ON_ILLEGAL_DATA

In addition, users can dynamically set the three variables with the following statements.

CALL "DCI_SETENV" USING "DCI_NULL_ON_ILLEGAL_DATA" "1"
CALL "DCI_SETENV" USING "DCI_NULL_ON_ILLEGAL_DATE" "1"
CALL "DCI_SETENV" USING "DCI_NULL_ON_SPACE_CHAR" "1"
CALL "DCI_SETENV" USING "DCI_NULL_ON_ILLEGAL_DATA" "0"
CALL "DCI_SETENV" USING "DCI_NULL_ON_ILLEGAL_DATE" "0"
CALL "DCI_SETENV" USING "DCI_NULL_ON_SPACE_CHAR" "0"

 

Version: DBMaker 5.2.x/5.3.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:54


How to use DCI_WHERE_CONSTRAINT under MF COBOL 4.1 on Linux? (DBMR2553)

From the end of January 2014, DBMaker DCI begin to support DCI_WHERE_CONSTRAINT. Users can use it by calling DCI_SET_WHERE. If want to use DCI_WHERE_CONSTRAINT under MF COBOL 4.1 on Linux, users can put the new lib libdmmfcbl.a under ~DBMaker/5.2/lib to use the new DCI, or modify the makefile and make rtsbmf, for example, changing from “cob -vgxo rtsdbmf -e "" +F DBMAKERINTF +F DCI_SETENV +F DCI_GETENV +F DCI_DISCONNECT\” to “cob -vgxo rtsdbmf -e "" +F DBMAKERINTF +F DCI_SETENV +F DCI_GETENV +F DCI_DISCONNECT +F DCI_SET_WHERE \”.

 

Version: DBMaker 5.2.x/5.3.x

Product: Normal/Bundle

Platform: Linux

Author: Sandy
Last update: 2016-03-18 08:57


What’s the path of the file DMDUMPxxxx_x.DMP? (DBMR2508)

DMDUMPxxxx_x.DMP is usually located under the database directory which will be recorded into DMERROR.LOG. However, if a user uses the database of which version is Bundle and is published before November 12, 2013, DMDUMPxxxx_x.DMP may be located under the directory C:\ when the database crashes.

Version: DBMaker 5.2.x

Product: Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:57


Which situations will cause “nested join” faster than “merge join”? (DBMR2559)

By default, if a table contains a small quantity of data, DBMaker will use “nested join” to query data. It scans part of data and takes less time. Conversely, if a table contains a great deal of data, DBMaker will use “merge join” to query data. It scans all data by scanning indexes and takes less time. However, if a table contains a great deal of data, and the SQL statements used to query data contains the clause “LIMIT”, the speed of querying data by using “merge join” is too slowly compared with “nested join”, so in this case, users, who use the version (of DBMaker 5.3) published after February 25, 2014, need to choose “nested join” by adding "DB_OPTRT = 1" into dmconfig.ini. This setting is available only for parts of query with LIMIT.

 

Version: DBMaker5.3.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:58


What’s the difference of “SET UNLOAD CHARTOHEX value” in different versions of DBMaker? (DBMR2567)

  • Ÿ   In DBMaker 4.1

The function of converting characters to HEX is not supported in DBMaker4.1, so all of the unloaded characters including unprintable characters can't be converted into HEX and will be unloaded forcibly without ensuring consistence. That is to say, in DBMaker 4.1, unprintable characters can't be successfully unloaded. Accordingly, they can't be loaded back to the database as completely same data successfully.

  • Ÿ   In DBMaker 4.3

“SET UNLOAD CHARTOHEX OFF” means the unprintable characters will be unloaded in format HEX, and other normal characters will be unloaded without any change.

”SET UNLOAD CHARTOHEX ON” means the whole data will be unloaded in HEX format.

  • Ÿ   In DBMaker 5.2.2/5.3.x

“SET UNLOAD CHARTOHEX OFF” means the whole data will not be converted into HEX. It is same with that in 4.1.

“SET UNLOAD CHARTOHEX ON” means the whole data will be unloaded in HEX format.

”SET UNLOAD CHARTOHEX AUTO” means the unprintable characters will be unloaded in format HEX, and other normal characters will be unloaded without any change. This function is same with the function of “SET UNLOAD CHARTOHEX OFF” in DBMaker 4.3.

 

Version: DBMaker 4.1/4.3/5.2.2/5.3.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 08:59


What reasons may cause “ERROR – unable to allocate an environment handle”? (DBMR2537)

This issue may be caused by the following two reasons.

  • Ÿ   Out of memory(this error may still cause IIS crashes).
  • Ÿ   The connection pool remains valid after the database is shut down, and continues allocating the environment handle.

 

Version: DBMaker 5.1.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 09:01


Why return “sun.jdbc.odbc.JdbcOdbcDriver” message by Jtools? (DBMR2596)

Those errors are mostly caused by a mismatch between DBMaker version and JRE version. Users can handle those errors by upgrading DBMaker or JRE.

 

Version: DBMaker 5.2.x/5.3.x

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 09:02


The questions and answers for using DBMaker + Hibernate? (DBMR2604)

Users should pay attention to the following FAQ:

Q 1: How to handle the error “java.lang.ClassNotFoundException: dbmaker.sql.JdbcOdbcDriver”?

A 1: Copy dmjdbc30.jar to x:\xxxx\lib.

Q 2: How to handle the error “java.lang.UnsatisfiedLinkError: no dmjdbc52 in java.library.path”?

A 2: set the value of the path environment variable to %path%;c:\dbmaker\5.2\bin.

Q 3:How to handle the error that Hibernate cannot find database name?

A 3:Copy DBMakerDialect .java to the relevant directory and make the following change in DataSource.groovy.

dataSource {

………..

dialect = org.hibernate.dialect. DBMakerDialect

}

Q 4:How to handle the error “java.sql.SQLException: parameter number not in valid parameter number range : 1~ 2

at dbmaker.sql.JdbcOdbc.JDBCError(JdbcOdbc.java:220)

at dbmaker.sql.JdbcOdbcPreparedStatement.setInt(JdbcOdbcPreparedStatement.java:478)”?

A 4:Replace findWhere with findAllWhere in the Controllers code (ex: UserController.groovy), and replace list(params) with getAll() in the Services code (ex: LoginService.groovy).

Version: All

Product: Normal/Bundle

Platform: Linux

Author: Sandy
Last update: 2016-03-18 09:03


With MF Visual Studio, how to use the WHERE condition in DCI both for main program and sub program (DBMR2608)

 Fill in “nofcd3 createxfd callfh "DBMAKERINTF.dll"” in the “Additional directives” field in the property page of the program, and put DBMAKERINTF.dll in the folder where the files locate created after the program being compiled,

 

Version: All

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 09:04


How to solve the error “dmserver cannot be connected or started when DB_UMODE=0”? (DBMR2612)

For old DB versions, DB_UMODE may be changed to 0 and cannot be reset to 1 even after restarting in some situations, for example: OS reboot immediately without waiting DB Server terminated successfully.

To solve these problems, users need to alter the value of DB_UMODE to 1 by hand in dmconfig.ini. This issue has been resolved in versions built after 2010/5/20, and don’t need to reset by hand.

Version: DBMaker 4.3.x, 5.1.X, 5.2.X

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 09:05


How to investigate the causes of ERROR (6590) while creating Stored Procedure? (DBMR2613)

1.Check <procedure_name>.msg, and according to the massage which included in it to investigate reasons.

2. If <procedure_name>.msg is empty, check the sp32.bat.

First, check whether the version of Visual Studio matches with the version which you installed, if not, please change registry key with the correct value, for example:

 [HKEY_LOCAL_MACHINE\SOFTWARE\DBMaker\5.3]

"COMPILER"="VC80"

Second, if Visual Studio is installed on 64-bit operation systems, users need to choose “x64 Compiler and Tools” option, otherwise users cannot create stored procedures successfully.

In additional, DBMaker support Visual Studio 2012 from DBMaker5.3.1(#23266, 20140807).

Version: All

Product: Normal

Platform: Windows

Author: Sandy
Last update: 2016-03-18 09:07


Why cannot install 64bit DBMaker setup on some x64 platform? (DBMR2629)

Author: Sandy
Last update: 2016-03-18 09:08


Why DBMaker performance degraded after upgrading from 5.0.x to 5.2.x?

After upgrading from 5.0.x to 5.2.x without using unload/load method, only starting same DB files with the higher version modules, due to the system catalog’s differences between 5.0.x and 5.2.x, will face poor performance. To solve this problem, users need to execute “update statistics” to upgrade the entire system catalog.

 

Version: DBMaker 5.0.x/5.2.x

Product: Normal

Platform: Windows/Linux

Author: Sandy
Last update: 2016-03-18 09:10


2015

How to create a table replication in one same database? (DBMR2758)

Generally, table replication is applied between different databases. However, it also can be used in one same database.

There are two ways:

First, use Create Replication syntax. But for this usage, the two options, "CLEAR DATA" and "CLEAR AND FLUSH DATA", cannot be contained in the Create Replication syntax.

For example:

CREATE REPLICATION rp_name

WITH PRIMARY AS SYSADM.FILE1

REPLICATE TO DBSAMPLE5:UTENTE.FILE1;

 

CREATE REPLICATION rp_name

WITH PRIMARY AS SYSADM.FILE1

REPLICATE TO DBSAMPLE5:UTENTE.FILE1

FLUSH DATA;

Note: If users create the replication with JDBA Tools, the button "Clear Data" cannot be checked.

If need to clear the original data of destination table, users should delete all records manually before creating the table replication.

Second, use triggers. Please note that this way only applies to a SYNC table replication, and it will occupy an extra connection as a global transaction.

For example:

CREATE TRIGGER TR_INSERT AFTER INSERT ON SYSADM.FILE1

FOR EACH ROW

(INSERT INTO UTENTE.FILE1 VALUES(new.c1,new.c2));

CREATE TRIGGER TR_UPDATE AFTER UPDATE ONSYSADM.FILE1

FOR EACH ROW

(UPDATE UTENTE.FILE1 SET C1=NEW.C1,C2=NEW.C2);

CREATE TRIGGER TR_DELETE AFTER DELETE ON SYSADM.FILE1

FOR EACH ROW

(DELETE FROM UTENTE.FILE1 WHERE C1=OLD.C1 AND C2=OLD.C2);

 

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:37


What's the meaning of "10054" and "[last func=32]" in the error message "ERROR (25350), connection already broken, database may already be shutdown or network may have failed [netio.c , 462],-1,0.10054 [last func=32]"? (DBMR2748)

 "[last = func 32]" is an internal definition and used for the developer to trace a position.

"10054" means a socket error which may be caused in many situations, such as shutdown, crash or network errors.

Users can find out which operation causes this error by checking the odbc log.

 

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:37


How to avoid the infrequent error "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified." in the process of a WEB application accessing ODBC? (DBMR2740)

 In the application, users can replace “DSN=XXXXX” with “"Driver=DBMaster 5.x.x Driver” in ODBC Connection String to avoid this error, for example, ODBC Connection String="Driver=DBMaster 5.x.x Driver;DATABASE=XXXXX;SVADR=127.0.0.1;UID=SYSADM;Pwd=".

 

Version: DBMaker 5.1.x

Product: Normal/Bundle

Platform: Windows

Author: finlay
Last update: 2016-06-17 04:38


What's the cause of ERROR(2405)? (DBMR2683)

ERROR(2405) is a license option error. If use an evaluation version not activated by any license numbers, users should consider the following limitations on the license.

1. Free Trial Period: 90 days

2. Max Connection: 5

3. Max Page Size: 8K

4. Max Journal File Size: 2G

 

Version: DBMaker 5.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:39


How to use foreign keys when a COBOL program accesses DBMaker with DCI? (DBMR2780)

In order to use foreign keys when a COBOL program accesses DBMker with DCI, users should ensure that the xml file matches the table.

Users need to change “open i-o” to “open output” in the COBOL program to create table “CET_ANALISE_PROPOSTA”, and then add foreign keys by hand, and finally change “open output” to “open i-o” before running the COBOL program.

 

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:39


How to check the running state for all of DBMaker's background processes on Linux? (DBMR2777)

DBMaker has 3 base processes on Linux: BACKUP_SERVER, SERVICE_SERVER and IO_SERVER. Users can use the following SQL statement to confirm the process id (the process id is same with value of the connection_id in table SYSUSER).

dmSQL> select connection_id,user_name from sysuser;

CONNECTION_ID          USER_NAME

================== ================

32433                    SYSADM

32322                    BACKUP_SERVER

32321                    SERVICE_SERVER

32320                    IO_SERVER

4 rows selected

Next time, users can use the following command to conform whether the database is active.

[dbmaker@RD-Redhat62 bin]$ ps -A|grep dmserver

32319 ? 00:00:00 dmserver.bin

32320 pts/0 00:00:00 dmserver.bin

32321 pts/0 00:00:00 dmserver.bin

32322 pts/0 00:00:00 dmserver.bin

32433 ? 00:00:00 dmserver.bin

If a connection is added, the number of processes will has an increase of 1; if a connection is disconnected, the number of processes will reduce by 1. For example, the above 32319 process is an added one.

In addition, if more users connect to db, more daemon processes will be added.

 

Version: All

Product: Normal/Bundle

Platform: Linux

Author: finlay
Last update: 2016-06-17 04:40


How to use the date in the format of "dd/mm/yyyy" or "dd-mm-yyyy" in DBMaker? (DBMR2773)

Users set DB_DaoFm in dmconfig.ini to control the date output format. Now DBMaker supports the following date formats.

mm/dd/yy

mm-dd-yy

dd/mon/yy

dd-mon-yy

mm/dd/yyyy

mm-dd-yyyy

yyyy/mm/dd

yyy-mm-dd

dd/mon/yyyy

dd-mon-yyyy

dd.mm.yyyy

DBMaker doesn't support "dd/mm/yyyy" or "dd-mm-yyyy", but users can use following way as a workaround.

select CAST(DAYOFMONTH(column_name) AS VARCHAR(10))|| '/'|| CAST(MONTH(column_name) AS VARCHAR(10))|| '/'|| CAST(YEAR(column_name) AS VARCHAR(10)) AS "DATE_FORMATED" from table_name;

 

Version: DBMaker 5.3.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:41


How to avoid ERROR (6534) when create a database by JServerManager Tool with old bundle version on Windows? (DBMR2490)

This error is caused by the conflict between libxml2.dll of JAVA7 and that of DBMaker. To avoid this error, users need to change the name of libxml2.dll in the same folder with javaw.exe to a new name or use DBMaker of new versions. In addition, this issue has been fixed after the versions of November 13, 2013.

 

Version: DBMaker 5.2.x/DBMaker 5.3.x

Product: Bundle

Platform: Windows

Author: finlay
Last update: 2016-06-17 04:43


How to avoid crash errors for executing a DCI case with isCOBOL2016R1? (DBMR2783)

When users execute DCI case in command lines, an error happens as following:

>java.exe ISMIGRATE

Exception in thread "main" java.lang.UnsatisfiedLinkError: com.iscobol.dci.Dci.dci__getenv(I[B[B)I

at com.iscobol.dci.Dci.dci__getenv(Native Method)

at com.iscobol.lib.DCI_GETENV.call(DCI_GETENV.java:43)

at com.iscobol.lib.DCI_GETENV.call(DCI_GETENV.java:24)

at com.iscobol.rts.Factory.call(Factory.java:3780)

at com.iscobol.rts.Factory.call(Factory.java:3689)

at ISMIGRATE.GET_DCI_CFG(ISMIGRATE.java:4382)

at ISMIGRATE.GUI(ISMIGRATE.java:2250)

at ISMIGRATE.MAIN(ISMIGRATE.java:2129)

at ISMIGRATE.perform(ISMIGRATE.java:1752)

at ISMIGRATE.call(ISMIGRATE.java:1736)

at ISMIGRATE.main(ISMIGRATE.java:43)

 

This problem is caused by the incompatible DCI library for isCOBOL2016R1. In order to fix MR10307 - "iscobol multi-thread with thin-client", both iscobol’s iscbol.jar and dbmaker’s dci lib have been modified, which makes JNI of DCI different from before.

 

That's to say, isCOBOL2016R1 must use the 2016 version of DCI library. In new DCI zip, users will find dci_2016.dll and libdci_2016.so and need to rename them to dci.dll and libdci.so separately for isCOBOL2016R1.

 

Version: DBMaker 5.4.x

Product: Bundle/Bundle

Platform: Windows/Linux

Author: finlay
Last update: 2016-06-17 04:44


Does DBMaker support using raw device to store a database's journals or files? (DBMR2769)

The Backup/Restore operation cannot be used for raw device in DBMaker's current versions, so please avoid using raw device when users need to backup/restore a database. In addition, DBMaker's some behaviors have changed on current Linux platform, therefore new versions of DBMaker will not support raw device in future.

 

Version: DBMaker 5.3.x/DBMaker 5.4.x

Product: Normal/Bundle

Platform: Linux

Author: finlay
Last update: 2016-06-17 04:45


Why the error "dmserver.bin: /lib64/libc.so.6: version ´GLIBC_2.7' not found (required by /home/dbmaker/5.4/lib/so/libxml2.so.2)” occurs when a user starts a database on Linux? (DBMR2769)

On 32 bit Linux platform, the version of libxml2.so used by DBMaker is libxml2.so.2.6.27, therefore GLIBC's version requirement still is 2.3.

On 64 bit Linux platform, the version of libxml2.so used by DBMaker 5.2 also is libxml2.so.2.6.27 and accordingly GLIBC's version requirement also is 2.3. However, the version of libxml2.so used by DBMaker 5.3 and DBMaker 5.4 is libxml2.so.2.9.1, therefore GLIBC's version requirement changes from 2.3 to 2.7 from DBMaker 5.3 accordingly.

Users can avoid this error by setting "auto update" for his Linux or upgrade GLIBC version by hand by referring to the following steps.

  1. Download GLIBC source code from http://ftp.gnu.org/pub/gnu/glibc/glibc-2.7.tar.gz
  2. >mkdir /usr/local/glibc
    >cd /usr/local/glibc
    >tar zxvf glibc-2.7.tar.gz -C /usr/local/src/
    >/usr/local/src/glibc-2.7/configure --prefix=/usr/
    > make
    > make install
  3. check GLIBC version with “ldd --version”

 

Version: DBMaker 5.3.x/DBMaker 5.4.x

Product: Normal/Bundle

Platform: Linux

Author: finlay
Last update: 2016-12-07 08:48


Why the error "invalid top directory at /usr/lib/perl5/5.8.8/File/Find.pm line 592” happens when "perl Makefile.PL" is executed? (DBMR2689)

Because the source code of DBD is too old and DBD only supports DBMaker 5.1 and previous versions, so a user can install DBMaker 5.1 firstly and, then can execute “perl Makefile.PL” successfully, or manually modify the Makefile.PL file in DBD, for example, if the user currently uses DBMaker 5.4, he need to change “my $DEFAULTVER = '5.1';” in Makefile.PL file to “my $DEFAULTVER = '5.4';”.

 

Version: DBMaker 5.3.x/DBMaker 5.4.x

Product: Normal/Bundle

Platform: Linux

Author: finlay
Last update: 2016-06-17 04:47


2016

How to convert hex type data to char or integer type data (DBMR2801)

You can use CAST to convert hex to char, and use BINTOINT to convert hex to integer type. Function BINTOINT supported in new versions built after 2016/02/23.

Usage examples:

dmSQL> select cast('6162'x as char(2));

CAST('6162'X AS CHAR(2))

========================

ab                      

dmSQL> select cast('ab' as binary(2));

CAST('AB' AS BINARY(2))

=======================

6162                   

 

dmSQL>CREATE FUNCTION strtoint.BINTOINT(BINARY(8)) RETURNS BIGINT;

dmSQL>select bintoint('07DF'x);

 BINTOINT('07DF'X)  

====================

                2015

Version: 5.3.3, 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-11 08:13


When use new feature -- “set unload fileobj name”?

“set unload fileobj name” was supported for keeping the FILE OBJECT not re-order during load to new DB.

About SYSTEM FILE OBJECT, default setting is [set unload fileobj on], and all of FILE OBJECT will be unload to current working directory.

When loading these unloaded files to new DB, all of SYSTEM FILE OBJECT would be reordered and the names would be changed as following samples.

In old DB before unloaded

ZZ000000.txt

ZZ000002.txt

ZZ000006.txt

In new DB after loaded

ZZ000000.txt

ZZ000001.txt

ZZ000002.txt

If the customers had quoted these FO names in their applications (for example: some pictures Link in Web Page), and don’t want to reorder these SYSTEM FILE OBJECT when migrating to new DB. They can set [set unload fileobj name]..

The data includes FO files would be unloaded, and then load these files to new DB.

FILE OBJECT can be checked, and would be listed as same as before:

ZZ000000.txt

ZZ000002.txt

ZZ000006.txt

Note: [set unload fileobj name] is supported in 4.3.4, 5.x which built after 2015/4/20 only. So please upgrade your current versions firstly if you want to use this feature.

Version: 4.3.4, 5.x (built after 2015/4/20)

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-11 08:46


Why journal full occurs and how to deal with it?

(1) Journal files size are too small

Journal size default value is 4M, if it's caused by journal size too small, you can increase the journal size as the following steps:

A. Stop DB.
B. Increase the size of DB_JNLSZ or add the number of DB_JnFil, and set   DB_SMODE=2 in dmconfig.ini.

C. Restart DB.

(2) Long transactions exist

  1. Split long transaction to some short transactions.
  2. Don’t execute "commit" for a long time when setting is “autocommit off”

(3) Backup mode is on

When backup server being turned on, lot of journal size will be hold until backup operation finished, so it’s better to execute backup in time.

(4) In table replication environment

  A. In our testing, journal full in master side caused by creating triggers while loading DB, the workaround is to use "column number" options (default 1000) as following:

   dmSQL> load db from xxxxx 100;

 B. If journal full happen in slave side, it should be caused by creating replication with "CLEAR and FLUSH", you can you turn off journal while loading DB to avoid journal full as following:

     dmSQL> set loaddb fast;

     dmSQL> load db from xxxxx 100;

     dmSQL> set loaddb safe;

Version: All version

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 07:09


How to use sleep function in SQL statements?

We support an UDF function sleep () for sleeping a while.

For example:

dmSQL> create function to_date.sleep(int) returns int;

dmSQL> select sleep(5);//sleep 5 seconds

Version: 5.3.x, 5.4.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-11 09:13


How to use LoadExternalDB to load DB/table/data online?

You can use LoadExternalDB to load external DB into local DB directly via DSN.

If set unload external 'connection string', dmSQL will print "set external db 'odbc connection string' " in unload-file.s0 file.

1. unload db1

Connect to db1 sysadm;

set unload external 'DSN=db1;UID=SYSADM;PWD=;';

unload db to db1;

2.  db1.s0 look like

set external db 'DSN=db1;UID=SYSADM;PWD=;';

create table SYSADM.T1 (

C1  INTEGER default null ,

C2  INTEGER default null )

in DEFTABLESPACE  lock mode row  fillfactor 100 ;

load external db from 'select * from SYSADM.T1' into SYSADM.T1;

3. load db to destination database:

Connect to db2 sysadm;

Load db from db1;  //commit_num is 1000 by default

OR

Load db from db1 100; //commit_num is 100

Note: Please make sure db1 started while loading to db2, and set DB_USRFO=1 in db2 in advance if user FO is used.

Version: 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:23


Why Update Statistics not work for rebuilding index sometime?

There are some reasons which can cause “update statistics” won’t be executed, for example: after migrate DB from 5.1 to 5.3, then execute update statistics and rebuild index, if you execute update statistics again, index statistics information won’t be updated.

We list the factors which will impact "update statistics" behaviors:

-----------------

1. New table and never execute "update statistics

2. The table's record numbers less than 20 pages

3. Added record numbers more than 2 pages

4. Update statistics operations had over 10 days

5. If execute update statistics operations on Table when meet above 1/2/3/4, and statistics info for indexes of this table will be updated.

We support a new SQL syntax to force execute "update statistics" as following.

>update statistics all; //[sample=100 by default]

>update statistics all sample=60;

Version: 5.3.3, 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:25


Why and How to rebuild Primary Key?

 When face such as below questions we can also consider to rebuild primary key.

For example:

  1. Too much index expansion to cause the performance down (IO and CUP cost)
  2. DB Crash cause the PK data inconsistent.

We can rebuild primary key with following usage:

Rebuild index PRIMARYKEY for t1; //in default DEFTABLESPACE

Rebuild index PRIMARYKEY for t1 in ts1; //support change tablespace only from 5.4 versions

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:27


How to encrypt table records for only some columns?

Can encrypt some columns with encryption UDF as following.

A. create function LIBCRYPT.AES_ENCRYPT(string,string) returns string;

  create function LIBCRYPT.AES_DECRYPT(string,string) returns string;

B. insert into tableXX(columnXX) values(AES_ENCRYPT(‘XXXXXX’, 'user_defined_key'));

 Or update tableXX set columnXX=AES_ENCRYPT(columnXX, 'user_defined_key');

C.  select AES_DECRYPT(column1, ‘user_defined_key’) from table1;

Version: 5.4.x

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:29


How to log CONNECTION and DISCONNECTION information only? (DBMR2835)

Setting DB_LGSVR=4,5,6 can record traced LOG which include CONNECTION and DISCONNECTION information, and also include SQL COMMAND, SQL COMMIT, SQL ROLLBACK, ERROR, and SLOW OPERATION.
If you only care about CONNECTION and DISCONNECTION information, or want to make the LOG file grow slowly, you can set DB_ LGSQL=0 in dmconfig.ini.

DB_LGSRV=4

DB_LGSQL=0

Note: In old versions, setting DB_LGSRV=4 and DB_LGSQL=0 will not ignore SQL COMMIT, SQL ROLLBACK which cause LOG file grow fast.

Version: 5.3.3, 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:31


Why lock-timeout happen while querying system tables? (DBMR2870)

 If you executed SQL statements in some simultaneous connections, for example: delete all records of one table, or drop/create table with "OUTPUT" in DCI, and the SYSTEM X LOCK will be added, and other connections cannot access this table. It's normal behavior.

But, even when you query some system table or check something in JDBA tool, “lock-timeout” also happens, which because system tables don’t allow dirty read.

If you want to query system table or check something in JDBA tool when SYSTEM X-LOCK exists, you can use “for browse” option as following:

>select * from SYSTABLE for browse;

Or set DB_DBIND=1 in dmconfig.ini (needs restarting DB)

DB_DBIND=1 //dirty system table binding

Or runtime set with

>call SETSYSTEMOPTION('DBIND', '1');

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:33


How to make LOAD DB more faster for huge DB? (DBMR2919)

LOAD DB support 2 option, safe and fast, the default is safe.

Set LOAD DB [safe | fast]

If set LOAD DB to SAFE, the database runs in normal mode, if an error occurs during loading ,the load utility rollbacks to the last committed command, and the error messages are displayed in the screen, and writes to the load utility’s log file.

If you want to speed up the LOAD DB process, you can set it to fast mode.

But if you set LOAD DB fast, below tips need to be pay attention.

1. Ensure BACKUP mode off during load process.

DB_BMODE = 0;(default value: 0)

Note: DB_BMODE keyword worked in server side, after change its setting, you need to restart db to apply.

Because "set loaddb fast;" worked under the no journal mode.

Set load fast means that you’re tried to turn on the NO JOURNAL mode, if your turn on backup mode at the same time, ERROR (1002) means was returned that because DBMaker cannot switch the journal mode while db is in BACKUP mode.

dmSQL>set loaddb fast;

2 If any error occurs, it will make the database shut down in no journal mode.

Version: All

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2017-01-12 03:36


Does the variable DB_JSYNC = 0 set in dmconfig.ini not work on DBMaker version 5.4.1?

This entry is in revision and can not be displayed.

Author: Sandy
Last update: 2019-08-20 16:55


2017

Why can find the errors with "check db" easily in slave DB? (DBMR2922)

If you executed "check db" command in slave side, the slave database read dirty data. It caused the data between master and slave database inconsistency. We advise that you don’t execute commands in slave side. It's better to check db in master side.

 

Version: 5.2.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 09:25


How to make a batch script for rebuilding all indexes? (DBMR2924)

Enter follow SQL commands to check and rebuild all indexes. For example, there is a database including five indexes.

 

  1. Check “CREATE_TIME” of original indexes.

 

dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;

 

dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX; CREATE_TIME                   INDEX_NAME

=================== ===================                                     

2020/01/31 14:55:02    SYSADM.T1

2020/01/31 14:55:13     SYSADM.T2

2020/01/31 14:56:23     SYSADM.T3

2020/01/31 14:57:34     SYSADM.T4

2020/01/31 14:58:45     SYSADM.T5

 

2. Get all of the rebuild indexes command.

 

dmSQL> SELECT 'REBUILD INDEX ' ||RTRIM(INDEX_NAME) || ' FOR ' || RTRIM(TABLE_OWNER) || '.' || RTRIM(TABLE_NAME) || ';' FROM SYSINDEX ORDER BY TABLE_OWNER,TABLE_NAME;

 

REBUILD INDEX IDX1 FOR SYSADM.T1;

REBUILD INDEX IDX2 FOR SYSADM.T2;

REBUILD INDEX IDX3 FOR SYSADM.T3;

REBUILD INDEX IDX4 FOR SYSADM.T4;

REBUILD INDEX IDX5 FOR SYSADM.T5;

 

3. Copy these rebuild commands into a script file, and run it.

 

dmSQL> run c:\rebuild.scp;

 

4. Execute step1 again. All indexes of “CREATE_TIME” updated.

 

dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;

 

CREATE_TIME                 INDEX_NAME

=================== ===================            

2020/01/31 16:14:40    SYSADM.T1                                              

2020/01/31 16:14:40     SYSADM.T2

2020/01/31 16:14:40    SYSADM.T3                                              

2020/01/31 16:14:40    SYSADM.T4                                              

2020/01/31 16:14:40    SYSADM.T5                                              

 

Version: 5.2.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-09-29 14:10


Why load external DB returned Segmentation fault at powerpc?

The unixODBC installing package for powerpc didn't include libodbc.so. You need to create the symbolic link manually. Enter below commands:

 

$ cd /usr/lib/powerpc64le-linux-gnu

$ ln -s libodbc.so.1.0.0 libodbc.so

 

Version: 5.4.1

Product: Normal/Bundle

Platform: Linux ppc64 3.19.0-28.31

Author: Sandy
Last update: 2020-04-22 09:50


Is DCI compatible with Acucobol v10? (DBMR2939)

DBMaker 5.4.2 or later version supports ACUCOBOL-GT 10. User can link dmacu90 library in Linux. Please refer the setup steps from DCI_README.TXT.

Notice: Please do NOT use the library and c files before Acucobol v10 version to build runtime.

Version: 5.4.2
Product: Normal/Bundle
Platform: Windows/Linux

Author: Sandy
Last update: 2020-07-22 15:10


How to start the database in single-user mode for maintenance? (DBMR2947)

You can use dmSQL or JDBA tool to maintain database in single-user mode. Follow below step to start the database for maintenance. Take database DBSAMPLE5 for example.

 

1. Using dmSQL tool

     a. Terminate the database.

     b. Run dmSQL tool.

     c. Start the database DBSAMPLE5.

          dmSQL> start db DBSAMPLE5 sysadm;

     d. Maintain the database.

     e. Terminate the database.

        dmSQL> terminate db;

 

2. Using JDBA tool in Windows

    a. Terminate the database.

    b. Open dmconfig.ini, comment DB_SVADR and DB_PTNUM, and save it.

    c. Open JDBA tool, and connect the database DBSAMPLE5.

    d. Maintain the database.

    e. Terminate the database.

    f. Open dmconfig.ini, uncomment DB_SVADR and DB_PTNUM, and save it.

 

Version: 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 11:36


How to avoid AbstractMethodError: dbmaker.sql.JdbcOdbcConnection.isValid(I)Z? (DBMR2960)

This error indicates that the version of DBMaker JDBC driver is outdated. Please update the driver to support isValid() method in JDBC. Before DBMaker 5.4.1, you have to set validationQuery = "select 1" in context.xml. We fix this error from DBMaker 5.4.1.

 

Version: 5.4.1

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 10:45


How to register OLE DB driver for bundle versions in Windows10 x64? (DBMR2996)

To install OLE DB driver bundle version in Windows x64, you need to register the driver manually. If you register the 32-bit driver, open cmd and enter below commands:

 

C:\> cd C:\dbmaker_bundle

C:\dbmaker_bundle> C:\Windows\SysWOW64\regsvr32.exe dmole52.dll

 

If you register the 64-bit driver, enter below commands:

 

C:\> cd C:\dbmaker_bundle

C:\dbmaker_bundle> C:\Windows\System32\regsvr32.exe dmole52.dll

 

In addition, there is different method to register OLE DB between normal and bundle version. The method to register bundle version is "Provider=DMOLE52B;Data Source=db_name;User Id=SYSADM;Password=;".

 

Note. DBMaker before bundle 5.2.3 version didn’t include dmole52.dll, you need to upgrade the DBMaker version from 5.2.3.

 

Version: 5.2.3

Product: Normal/Bundle

Platform: Windows 10

Author: Sandy
Last update: 2020-04-22 10:51


What connection string does DBMaker support to connect ODBC? (DBMR3000)

DBMaker support below connection strings:

DRIVER: ODBC driver version.

DATABASE: Database name.

DSN: Data source name.

UID: User ID.

PWD: Password.

ERRLCODE: Set client's error message character set.

CLILCODE: Set the language code in the client side.

SVADR: Server address.

PTNUM: Port number.

ATCMT: Set autocommit on or off.

LTIMO: Set lock time out (5.4.3 #29936, 20200708).

CTIMO: Set connection time out.

STRSZ: This keyword indicates the length of returned data of string type, used only by user-defined function (UDF).

STROP: This keyword specifies whether space padding removed before applying the string concatenation operator (||).

DSCMT: This keyword specifies whether to commit a transaction when an application is disconnecting from the database.

 

Version: 5.4.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-07-09 14:51


How to make the uncommitted records rollback in slave DB?

If master database terminated db before some transactions hadn’t been committed, these
uncommitted records had been existed in slave database and didn’t be rollback automatically. You have to start with setting DB_SMODE = 1, the database rollback automatically.

Author: Sandy
Last update: 2020-04-22 10:54


2018

How to connect DBMaker with an Android client application? (DBMR2987)

You need to set dmjdbct3c.jar in external classpath, then change driver class and URL on your code. Add below code to connect DBMaker for Android applications.

 

Class.forName("dbmaker.jdbc.ws.client.Driver");

Connection con = DriverManager.getConnection(

"jdbc:dbmaker:type3://IP:port/db_name", "UID", "PWD");

 

Version: DBMaker 5.4.2, Android Studio 3.5 with API-25

Product: Normal/Bundle

Platform: Windows

Author: Sandy
Last update: 2020-04-22 11:04


What is the fastest way to compare two tables? (DBMR3010)

The “EXCEPT” operator is used to combine two “SELECT” statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. DBMaker does not support EXCEPT operator. Therefore, we give an alternative workaround. However, the command only compared two columns and cannot compare BLOB data.

 

       This command replaces “table t1 except table t2”.

 

select * from t1 where not exists(select 0 from t2 where t1.c1 = t2.c1 and (t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

       This command replaces “table t2 except table t1”

 

select * from t2 where not exists(select 0 from t1 where t1.c1 = t2.c1 and(t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

       For example, there are two tables, t1 and t2.

 

Table t1

 

C1       C2

======= =======

       1         1

       2         2

       3         3

 

Table t2

 

C1       C2

======= =======

       1         1

       2         2

       4         4

 

      Execute the first command to replace “t1 except t2”. You can get 3 which is in t1 but not in t2.

 

dmSQL> select * from t1 where not exists(select 0 from t2 where t1.c1 = t2.c1 and (t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

C1       C2

======= =======

       3        3

 

       Execute the second command to replace “t2 except t1”. You can get 4 which is in t2 but not in t1.

 

dmSQL> select * from t2 where not exists(select 0 from t1 where t1.c1 = t2.c1 and(t1.c2 = t2.c2 or (t1.c2 is null and t2.c2 is null)));

 

C1       C2     

======= =======

       4        4

 

Version: DBMaker 5.4.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 11:33


Is possible create the view materialized in DBMaker? (DBMR3096)

Materialized view is the physical copy of the original base tables. Unlike view, the materialized view is precomputed and stored on a disk, and they are not updated each time they are used. DBMaker doesn't support materialized view, so we give some assumptions for each workaround.

 

1. Do materialized view: "refresh complete" + "on commit"

(a) Try to create a temp/permanent table (mv1), if table exists, do (b).

(b) As view definition, insert data to mv1 using "select into".

(c) Create a sp (mvsp1) which delete all records from mv1, and do (b).

(d) After insert/update/delete trigger on table A or table B to call mvsp1.

 

2. Do materialized view: "refresh complete" + "start with・next"

(a)  Try to create a temp/permanent table (mv1).

(b)  As view definition, insert data to mv1 using "select into".

(c)  Create a sp (mvsp1) which delete all records from mv1, and do (b).

(d) Create task on schedule daemon to execute call mvsp1.

 

3. Do materialized view: "refresh complete" + "on demand"

(a)  Try to create a temp/permanent table (mv1).

(b)  As view definition, insert data to mv1 using "select into".

(c)  Create a sp (mvsp1) which delete all records from mv1, and do (b).

(d)  Call mvsp1 on demand.

 

4. Do materialized view: "never refresh" + "on demand"

The same as 3, maybe user wants this.

 

5. Do materialized view: "refresh fast/refresh force" + "on commit" (PK cannot be updated)

(a)  Try to create a temp/permanent table (mv1).

(b)  As view definition, insert data to mv1 using "select into".

(c)  Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)

(d)  After insert/update/delete trigger on table A or table B to call mvsp1.

 

6. Do materialized view: "refresh fast/refresh force" + "start with・next" (PK cannot be updated)

(a)  Try to create a temp/permanent table (mv1).

(b)  As view definition, insert data to mv1 using "select into".

(c)  Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)

(d)  Create task on schedule daemon to execute call mvsp1.

 

7. Do materialized view: "refresh fast/refresh force" + "on demand" (PK cannot be updated)

(a)  Try to create a temp/permanent table (mv1).

(b)  As view definition, insert data to mv1 using "select into".

(c)  Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)

(d)  Call mvsp1 on demand.

Author: Sandy
Last update: 2020-04-22 11:14


When is it needed to set DB_UPGDB = 0? (DBMR3111)

If you created database replication before DBMaker 5.4.3, and the version of master and slave database was different, it might cause some incompatibilities issue. We advise you to update the same version between master and slave database or slave side update to DBMaker 5.4.3.

 

       From DBMaker 5.4.3, we add a new key word “DB_UPGDB” in dmconfig.ini. Set DB_UPGDB 1 or 0 to update the database version or not when you update DBMaker version. The default value is 1. If the version of master database is older than slave database, you can set DB_UPGDB to 0 for slave database. Let the version of master and slave database are same. After set DB_UPGDB to 0, you have to recopy master database file to slave database.

 

Version: DBMaker 5.4.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 11:15


How to connect to multi database with different LCODE simultaneously? (DBMR3134)

 

       From DBMaker 5.4.3, we fix the problem. You can connect to multi database with different LCODE simultaneously.

 

It is a restriction before DBMaker 5.4.3. DBMaker cannot connect to multi database with different LCODE setting simultaneously. You can connect another database after closing first connection. If you cannot close first connection, and we found a workaround as following steps:

 

  1. Install another DBMaker ODBC driver in different directory.
  2. Create DSN for second database.
  3. Use Sun JDBC driver (1.7 or lower JDK) in JAVA code to connect second DB.
  4. From JDK 1.8, Sun JDBC wouldn't be supported. Therefore, from DBMaker 5.4.2, we added a new JDBC type (dbmaker-odbc) to replace Sun JDBC to connect other Database for heterogeneous asynchronous table replication.

 

For this issue, we can also use this new JDBC type.

Class.forName("dbmaker.sql.JdbcOdbcDriver");

Connection con = DriverManager.getConnection("jdbc:dbmaker:DB_NAME","UID","PWD");

 

Version: DBMaker 5.4.2

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-22 11:16


How to connect DBMaker via Microsoft SQL Linked Server? (DBMR3153)

There are two providers to use MS linked server to connect DBMaker. They are Microsoft OLE DB Provider for ODBC Drivers and OLE DB Provider for DBMaker 5.4.3. Please follow below step to connect DBMaker.

 

1. Provider for Microsoft OLE DB Provider for ODBC Drivers

    a. Open ODBC data source administrator. Add the database to system data source name.

    b. Open Microsoft SQL Server Management Studio, open object explorer, click server object, right-click linked servers, and click new linked server.

    c. Enter linked server name, choose provider Microsoft OLE DB Provider for ODBC Drivers, enter database name to data source.

    d. Start database server from DBMaker.

    e. Click Ctrl + N to open new query, and enter SQL query.

2. Provider for OLE DB Provider for DBMaker 5.4.3

    a. Open Microsoft SQL Server Management Studio, open object explorer, click linked server, click provider, double-click “DMOLE54”, and tick “AllowInProcess”.

    b. Open object explorer, click server object, right-click linked servers, and click new  linked server.

    c. Enter linked server name, choose provider OLE DB Provider for DBMaker 5.4.3, enter database name, data source and connection string to data source.

   d. Start database server from DBMaker.

   e. Click Ctrl + N to open new query, and enter SQL query.

 

Version: DBMaker 5.4.3, SQL Server 2016, SQL Server Management Studio 18

Product: Normal/Bundle

Platform: Windows

Author: Sandy
Last update: 2020-08-11 11:15


How to install JDBC type3 as Windows Service? (DBMR3162)

1. Unzip attached JettyService.zip (3 files) in dbmaker\5.4\jetty

    a. install-service.bat

    b. JettyService.exe

    c. bin\JettyService.exe

 

2. Run cmd as Administrator

    > cd dbmaker\5.4\jetty

    > install-service.bat

    Note: you can edit install-service.bat if necessary.

 

3. > JettyService.exe

    Set service with GUI => "DMJDBC Type3 Service"

 

Author: Sandy
Last update: 2020-04-22 12:15


Why doesn’t NT Service write on network drive? (DBMR3177)

If you get ERROR(11213): File or directory does not exist or ERROR(11206): Cannot
create/open a file, this issue caused by restriction of Windows NT service. It's
hard to fix it in DBMaker. Using dmservice can’t use “map network driver”.
Please set DB_BKDIR directory with double backslash(\\). For example, set DB_BKDIR
= \\192.168.1.1\backup.

Author: Sandy
Last update: 2020-04-22 12:19


2019

How to comment code in SQL stored procedure? (DBMR3183)

You can use these symbols “/* */”, “//”, “#” and “--” to comment code. Using symbol
“/* */” to comment code inside the symbol. Using the symbols “//”, “#” and “--” to comment the line after the symbol.

Author: Sandy
Last update: 2020-04-23 05:41


How to restore table with another table name from Full Backup? (DBMR3188)

It is not easy to support restore table with another name from a full backup. Maybe we need to support more unload syntax to support it. Here is an alternative workaround using SQL command without full backup.

 

1. Unload table to the file previously.

dmSQL> unload table from table_name to file_name;

2. Load table from the file.

dmSQL> load table from file_name;

3. If you want to rename the table, type below command.

dmSQL> alter table rename table_name to new_table_name;

 

We provide another workaround. Dmrestoretb is a tool which can restore table by full backup. From version 5.4.3, we add a new feature to restore table with target name. If you want to know the detail usage of dmrestoretb, open cmd or terminal, go to dbmaker/5.4/bin directory, and type dmrestoretb. Enter below command to restore table_name to target_name.

 

> dmrestoretb db_name table_name -t target_table

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 04:13


How to get all index columns via query? (DBMR3196)

From DBMaker 5.4.3, we add a stored procedure command “showindex”. Enter below command, you can get the index column.

 

dmSQL> call showindex(owner_name, table_name);

 

Here is an example. There is a table t1 in database. Enter stored procedure command “call showindex”, you can get all index columns.

 

dmSQL> call showindex(‘sysadm’, ‘t1’);

 

TABLE_OWNER TABLE_NAME NON_UNIQUE INDEX_NAME TYPE ORDINAL_POSITOIN

=========== ========== ========== ========== ==== ================

SYSADM       T1                      0 PRIMARY*       3                   1

SYSADM       T1                      1 IDX1            3                   1

SYSADM       T1                      1 IDX2            3                   1

SYSADM       T1                      1 IDX3            3                   1

SYSADM       T1                      1 IDX3            3                   2

 

COLUMN_NAME ASC_OR_DESC CARDINALITY PAGES FILTER_CONDITION FOREIGN_KEY

=========== =========== =========== ===== ================ ===========

ID          A              NULL             -1 NULL                        0

ID          A              NULL             -1 id > 2                      0

NAME        A              NULL             -1 name like '%*               0

ID          A              NULL             -1 id < 3 and n*               0

NAME        A              NULL             -1 id < 3 and n*               0

 

Version: DBMaker 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

 

Author: Sandy
Last update: 2020-04-23 04:18


How to get table schema via query? (DBMR3207)

From DBMaker 5.4.3, you can get table schema via dmSQL or JDBA tool.

 

1. There is a table t1 in the database. Enter below command, you can get table schema.

 

 dmSQL> def table t1;

 

 create table SYSADM.T1(

ID INTEGER not null  ,

NAME CHAR(20) default null)

in DEFTABLESPACE lock mode row fillfactor 100;

create index IDX1 on SYSADM.T1(ID asc) where id > 2 in DEFTABLESPACE;

create index IDX2 on SYSADM.T1(NAME asc) where name like '%a' in DEFTABLESPACE;

create index IDX3 on SYSADM.T1(ID asc, NAME asc) where id < 3 and name like '%p%' in DEFTABLESPACE;

alter table SYSADM.T1 primary key (ID) in DEFTABLESPACE;

 

2. Using JDBA tool.

    a. Open JDBA tool.

    b. Connect to the database.

    c. Right-click the table

    d. Click “Define table”, you can see the table schema in right side.

 

In addition, if you want to get schema with SQL statement, you can enter below command.

dmSQL> unload schema from table_name to file_name;

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 04:25


How to avoid shrinking database with journal full? (DBMR3233)

Shrinking database could be rolled back, which meant your “shrink” operation required journal file, and the required size should be double of compressed data and BLOB. If the capacity of journal is not enough, you can shrink database with setting journal off. Open dmsqls with single user mode. Before you execute below commands, please backup database.

 

dmSQL> start db db_name sysadm;

dmSQL> set journal off;

dmSQL> shrink tablespace ts_name compressonly;

dmSQL> set journal on;

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

 

Author: Sandy
Last update: 2020-04-23 04:28


How to export and import data to JSON format?

Enter below code to create store procedure “EXPORTJSON”. This stored procedure can export table to JSON format.

 

dmSQL> set block delimiter @@;

dmSQL> @@

CREATE OR REPLACE PROCEDURE EXPORTJSON(usr varchar(128),tab varchar(128),expath varchar(256),filter varchar(256))

LANGUAGE SQL

BEGIN

  DECLARE STR CHAR(128);

  DECLARE NCOL INT;

  DECLARE COLNAME varchar(128);

 

#get col num

  set STR='SELECT NUM_COL FROM SYSTABLE WHERE TABLE_NAME = upper(?) AND TABLE_OWNER = upper(?)';

  PREPARE stmt FROM str;

  EXECUTE stmt INTO NCOL USING tab,usr;

  DEALLOCATE PREPARE stmt;

  IF NCOL = NULL THEN

    return 6521,'table or view does not exist';

  END IF;

 

<lua>

   gbc={}

   collist={}

</lua>

 

#get col list

  set STR='SELECT TRIM(COLUMN_NAME) FROM SYSTEM.SYSCOLUMN WHERE TABLE_NAME = upper(?) AND TABLE_OWNER = upper(?) ORDER BY COLUMN_ORDER';

  PREPARE stmt FROM str;

  DECLARE cur1 CURSOR FOR stmt;

  OPEN cur1 USING tab,usr;

  WHILE SQLCODE = 0 DO  

    FETCH cur1 INTO COLNAME;

<lua>

   table.insert(collist,sv['COLNAME']:getval())

</lua> 

  END WHILE;

  CLOSE cur1;

  DEALLOCATE PREPARE stmt; 

 

  IF usr is not null and usr <> '' THEN

    set STR='SELECT * FROM ' || usr || '.' || tab ;

  ELSE

   set STR='SELECT * FROM ' || tab ;

  END IF;

 

  IF filter is not null and filter <> '' THEN

    set STR = STR || ' WHERE ' || filter;

  END IF;

 

  PREPARE stmt FROM STR;

  DECLARE cur2 CURSOR FOR stmt;

  OPEN cur2;

  WHILE SQLCODE = 0 DO

  <lua>

    precall(outcon)

    --FETCH Cursor

    if tblcur["CUR2"] ~= nil then

       rs_CUR = tblcur["CUR2"]:fetch({}, "n", "NEXT")end

    if type(rs_CUR) == "table" then  

      local tmp={}

      for k= 1,sv['NCOL']:getval(),1 do

      if type(rs_CUR[k]:getval()) == 'table' then

        tmp[collist[k]] = rs_CUR[k]:tostring()  --datatime

      else

        tmp[collist[k]] = rs_CUR[k]:getval()

      end

      end

      table.insert(gbc,tmp)

    end

    postcall(outcon)

  </lua>

  END WHILE;

  CLOSE cur2;

 

<lua>

  ff=io.open(sv['EXPATH']:getval(),'w')

  if not cjson then cjson = require 'cjson' end

  ff:write(cjson.encode_escape(gbc))

  ff:close()

</lua> 

  DEALLOCATE PREPARE stmt;

END;

@@

 

Enter below command to export table to JSON format.

dmSQL> call exportjson(owner_name, table_name, file_name, filter);

 

Enter below code to create stored procedure “IMPORTJSON”. This store procedure import JSON to table data.

 

dmSQL> set block delimiter @@;

dmSQL> @@

CREATE OR REPLACE PROCEDURE IMPORTJSON(usr varchar(128),tab varchar(128),expath varchar(256))

LANGUAGE SQL

BEGIN

  DECLARE STR CHAR(128);

  DECLARE NCOL INT;

  DECLARE VAR INT;

  DECLARE COLNAME varchar(128);

  DECLARE COLTYPE INT; 

 

#get col num

  set STR='SELECT NUM_COL FROM SYSTABLE WHERE TABLE_NAME = ? AND TABLE_OWNER = ?';

  PREPARE stmt FROM str;

  EXECUTE stmt INTO NCOL USING tab,usr;

  DEALLOCATE PREPARE stmt;

  IF NCOL = NULL THEN

    return 6521,'table or view does not exist';

  END IF;

 

<lua>

  collist={}

  coltype={}

  ff=io.open(sv['EXPATH']:getval(),'r')

  if not cjson then cjson = require 'cjson' end

  gbc = cjson.decode(ff:read('a'))

  ff:close()

</lua>

 

#get col list

  set STR='SELECT TRIM(COLUMN_NAME),DATA_TYPE FROM SYSTEM.SYSCOLUMN WHERE TABLE_NAME = ? AND TABLE_OWNER = ? ORDER BY COLUMN_ORDER';

  PREPARE stmt FROM str;

  DECLARE cur1 CURSOR FOR stmt;

  OPEN cur1 USING tab,usr;

  WHILE SQLCODE = 0 DO  

    FETCH cur1 INTO COLNAME,COLTYPE;

<lua>

   table.insert(collist,sv['COLNAME']:getval())

   table.insert(coltype,sv['COLTYPE']:getval())

</lua>

  END WHILE;

  CLOSE cur1;

  DEALLOCATE PREPARE stmt; 

 

  IF usr = '' THEN

    SET STR= 'INSERT INTO ' || tab || ' VALUES(';

  ELSE

    SET STR= 'INSERT INTO ' || usr || '.' || tab || ' VALUES(';

  END IF;

 

  set VAR = 0;

  WHILE VAR < NCOL DO

    IF VAR = 0 THEN

      SET STR= STR || '?';

    ELSE

      SET STR= STR || ',?';

    END IF;

    SET VAR = VAR + 1;

  END WHILE;

  SET STR= STR || ')';

 

  PREPARE stmt FROM STR;

 

# EXECUTE stmt USING VAR,COLNAME;

<lua>

  for k=1,rawlen(gbc),1 do

  local slice=gbc[k]

  precall(outcon)

  --EXECUTE 

      local exec_args_table={}

      for i=1,sv['NCOL']:getval(),1 do

        if coltype[i] == 1 or coltype[i] == 12 or coltype[i] == -1 or

           coltype[i] == 9 or coltype[i] == 10 or coltype[i] == 11  or coltype[i] == -4 then

          table.insert(exec_args_table,DTO.varchar(rawlen(slice[collist[i]]),slice[collist[i]]))

      elseif coltype[i] == 3 or coltype[i] == 6 or  coltype[i] == 7 or coltype[i] == 8 then

          table.insert(exec_args_table,DTO.double(slice[collist[i]]))

        elseif coltype[i] == 4 or coltype[i] == 5 or coltype[i] == -5 then

          table.insert(exec_args_table,DTO.bigint(slice[collist[i]]))

        elseif coltype[i] == -8 or coltype[i] == -9 or coltype[i] == -10 then

          table.insert(exec_args_table,DTO.nvarchar(rawlen(slice[collist[i]]),slice[collist[i]]))

        elseif coltype[i] == -2 or coltype[i] == -3  then

          table.insert(exec_args_table,DTO.binary(rawlen(slice[collist[i]]),slice[collist[i]]))

        else

          table.insert(exec_args_table,DTO.int(nil))

      end

      end  

    sv("STMT", outcon:execute(sv["hstmt_STMT"], exec_args_table, nil, nil))

  postcall(outcon)

  end

</lua>

  DEALLOCATE PREPARE stmt;

END;

 

Enter below command to import data from JSON format.

dmSQL> call importjson(owner_name, table_name, file_name);

 

Version: DBMaker 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 05:27


How to set system file object extension name in PHP via PDO ODBC? (DBMR3280)

Without loading file

$DB = new PDO('odbc:mydb', 'sysadm', '');

 

$filePath = '/tmp/demo.html';

$id = 1;

 

$sth = $DB->prepare("update demo set f = ? where id = ?");

if ($sth == false) {

  var_dump($DB->errorInfo());

}

 

$result = $DB->exec("SET EXTNAME TO 'html'");

echo "affected $result rows\n";

 

$result = $DB->exec("SET INPUT PARAM 1 AS CFILE");

echo "affected $result rows\n";

 

$sth->bindParam(1, $filePath);

$sth->bindParam(2, $id);

 

$result = $sth->execute();

if ($result == false) {

  var_dump($DB->errorInfo());

}

 

With loading file

$DB = new PDO('odbc:mydb', 'sysadm', '');

 

$filePath = '/tmp/demo.html';

$id = 1;

$DB = new PDO('odbc:mydb', 'sysadm', '');

 

$filePath = '/tmp/demo.html';

$id = 1;

 

$sth = $DB->prepare("update demo set f = ? where id = ?");

if ($sth == false) {

  var_dump($DB->errorInfo());

}

 

$result = $DB->exec("SET EXTNAME TO 'html'");

echo "affected $result rows\n";

 

$result = $DB->exec("SET INPUT PARAM 1 AS CFILE");

echo "affected $result rows\n";

 

$sth->bindParam(1, $filePath);

$sth->bindParam(2, $id);

 

$result = $sth->execute();

if ($result == false) {

  var_dump($DB->errorInfo());

}

 

Author: Sandy
Last update: 2020-04-23 05:29


How to fix invalid view? (DBMR3289)

If you dropped the table, then recreated the table, the view which was based on the table became invalid view. When you select the invalid view, you will get ERROR(6539): This view exists but it is invalid. Before DBMaker 5.4.3, you have to enter below command to recompile the view.

 

dmSQL> alter view view_name compile;

 

From DBMaker 5.4.3, we add new features when you create or alter table:

  1. When creating table, DBMaker will automatically do “ALTER VIEW COMPILE” for all invalid view.
  2. When altering table, DBMaker will automatically do “ALTER VIEW COMPILE” with dependency.

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 05:32


How much is the Key length for network encryption? How to work? (DBMR3291)

To start network encryption, set DB_NETEC to 1 in dmconfig.ini. All data is encrypted by DES, and transmits to clients. When server creates the connection, it also creates a DES key, which has 56 and 8 parity bit in 8 bytes. To avoid the key being stolen or decrypted, system uses RSA to encrypt the DES key to transmit to slave side. If you are worried that DES encryption is not enough safe, you can create a SSH tunnel to transmit data.

 

[DBSAMPLE5]

DB_SVADR=localhost

DB_PTNUM=2453

 

$ ssh -L 12453:server.com:2453 user@server.com

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 05:34


What is minimum length of column to save AES_ENCRYPT data?

After using AES_ENCRYPT function, the data is encrypted and enlarged. If the length of column was not enough to save data, the system got ERROR (8072): data encrypt or decrypt fail. The length of encrypted data is the multiple of 16. Therefore, the minimal length of column is (int)(data length / 16 + 1) * 16. For example, if one data length is 28, the encrypted data length is (int)(28 / 16 + 1) * 16 = 32.

 

Here is an example for using AES_ENCRYPT function. We create table t1 and t2, the length of t1.c2 and t2.c2 is 16 and 15. We add one data which length is 15 into t1 and t2 separately. Because t2.c2 was not enough to save encrypted data, system got ERROR (8072).

 

dmSQL> create table t1(c1 int, c2 varchar(16));

dmSQL> create table t2(c1 int, c2 varchar(15));

 

dmSQL> create function libcrypt.aes_encrypt(varchar(32), string) returns varchar(32);

dmSQL> create function libcrypt.aes_decrypt(varchar(32), string) returns varchar(32);

 

dmSQL> insert into t1 values(1, aes_encrypt('123456789012345', 'abc'));

dmSQL> insert into t2 values(2, aes_encrypt('123456789012345', 'abc'));

dmSQL> select c1, aes_decrypt(c2, 'abc') from t1;

 

  C1    AES_DECRYPT(C2, 'ABC')    

===== ===========================

     2 123456789012345

 

dmSQL> select c1, aes_decrypt(c2, 'abc') from t2;

 

  C1    AES_DECRYPT(C2, 'ABCDEF')    

===== ===========================

ERROR (8072): [DBMaker] data encrypt or decrypt fail

 

Version: 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Author: Sandy
Last update: 2020-04-23 05:36


Does DBMaker support Resultset.isLast() in JDBC

DBMake doesn’t support Resultset.isLast() method in JDBC. You can use Resultset.next() to check the cursor is in last position of resultset or not. If ResultSet.next() returns false, it will be the last record. If not, you can move previous to make cursor back.

Author: Sandy
Last update: 2020-04-23 05:37


How to add single quotation mark in char compatible type?

You have to add two single quotations in string. For example, if you want to add “Hello, I’m DBMaker!” in table t1, you can type “insert into t1 values(‘Hello, I’’m DBMaker!’);”

Author: Sandy
Last update: 2020-04-23 05:39


Why does DBMaker return null when using QueryRunner or ResultSet function in JDBC?

It’s a problem of org.apache.commons.dbutils.QueryRunner. If you have the same column name in different table, you have to use “as” syntax to set alias. For example, if table t1 and t2 have the same column c1, you can type “t1.c1 as t1_c1” to set the alias of t1.c1.

Author: Sandy
Last update: 2020-04-23 05:40


2020

How to fix ERROR(7302) or ERROR(7303) when create Linked Server in MS SQL Server? (DBMR3397)

For detail you can refer to http://www.lingan.com.cn:8117/faq/?sid=10&lang=en&action=faq&cat=72&id=825&artlang=en&highlight=dbmr3153 , and try to set AllowInProcess=1. Or set in "Microsoft SQL Server Management Studio":

Server Objects      
     -> Linked Servers         
              -> Providers          
                        -> DMOLE54        
                                   -> enable "AllowInProcess"
 

Version: DBMaker 5.4.3, SQL Server Management Studio 14
Product: Normal/Bundle
Platform: Windows  

Author: Sandy
Last update: 2020-08-14 14:22


Why not return OPTION_VALUE value but be asked to input value when call getsystemoption('xxxxx',?) ?

When new client connects to old server. Call getsystemoption('xxxxx',?) get weird result. It should return OPTION_VALUE value but be asked input value. It was caused by client server is difference version, please try to be consistent version.

Weird result as following:

dmSQL> call getsystemoption('bksvr',?);
dmsql/Val>  

Normal behavior should be similar as following:

dmSQL> call getsystemoption('bksvr',?);
OPTION_VALUE                    : 0  

Version: DBMaker 5.4.1 server + DBMaker 5.4.3 client
Product: Normal/Bundle
Platform: Windows

Author: Sandy
Last update: 2020-09-28 10:25


Where does the driver stores informations when creating DSN?

We got the Question you added in FAQ system. In fact, you can ask any question by sending mail to support_rd@syscom.com.tw And the FAQ system is only for us to publish some Question & Answer.

About your question:

  2020-10-08 23:33
GIOVANNI ROSINI
2020
When I create a system DSN using ODBC driver, username is stored somewhere so that, when I try to change it, previous user is automatically prompted as soon as I choose database name, and it's impossible for me to use a different user, also if I delete the DSN and recreate it. I can change server ip and port, but not the username. Where does the driver stores informations? Thanks



Answer:
Any DB setting will be stored in dmconfig.ini, and you can add DB Name, modify user name/password, IP, port and so on in dmconfig.ini.
You can choose DB name in combox when creating ODBC DSN, and don't need to modify user name/password, IP, port.
Certainly, you can modify DSN name there.

Author: ququ
Last update: 2020-10-10 15:47