Tibero part 2. Relocation of the project from Oracle
The first part of my adventures with Tibero here
To test the compatibility of Tibero with Oracle, I decided to check whether my favorite PL / SQL libraries would work on it, namely:
https://github.com/mortenbra/alexandria-plsql-utils/
Testing began with JSON_UTIL_PKG . A very useful feature that allows you to quickly generate a JSON response.
and so we do
To simplify the life of the beginning of the pkb file, add
types.sql installed successfully.
When installing the package, we unexpectedly stumble upon problems. If in oracle the installation of these three files went smoothly with getting the compiled package, then in the case of tibero when filling the package body I received the following conclusion:
Calling
I get an error
It turns out that there is no dbms_xmlgen package in tibero . Faced with this problem, I decided to see what kind of DBMS% packages are. Request
Got the following list:
Total 45 vs. 195 in my version of oracle . It looks sad. From those packages that I had to use in Oracle , the lack of DBMS_SCHEDULER , DBMS_XMLDOM , etc. upsets.
I think that many in the list of 150 who did not get into tibero dbms will find packages that are not enough for them.
But what is most interesting: the dbms_xmlgen package, due to which there was an error, is present in Tibero . The problem turned out to be that there is no setmaxrows function in the package.
By making
Got such a content package in Tibero
As can be seen from the above, Tibero has fewer not only packages, but also procedures inside them. It turns out that most likely the migration of the finished Oracle product to Tibero will be quite difficult, because there is a great chance that packages using Oracle standard packages are not compiled and have to be rewritten, or you can independently implement standard Oracle packages.
')
Having received such a result, I decided to study two more packages in more detail: DBMS_LOB and DBMS_LOCK . They were chosen because they seem to me the most popular among PL / SQL developers.
DBMS_LOB.
The comparison showed that all the basic functions are present, but it is impossible to work with fragments in Tibero , c DBFS_LINK also, and some convenient methods either. Speaking dryly, the DBMS_LOB package in tibero does not contain the following methods:
DBMS_LOCK is fully implemented.
With UTL_% packets, the situation is close to DBMS_% . There are 11 packages in total at Tibero . Of which 8 ( UTL_ENCODE , UTL_FILE , UTL_HTTP , UTL_I18N , UTL_MATCH , UTL_RAW , UTL_TCP , UTL_URL ) are similar to standard Oracle packages, the remaining three are their own. Oracle, in turn, has packages of 21, thus the following 13 packages are not implemented in Tibero :
I wonder why Tibero doesn't like XML ...
The state of the libraries is not very optimistic. But not always we use DBMS and UTL packages! Sometimes there are enough standard functions, and with them everything is more or less optimistic. TMax actively expands the list of supported functions and now stably supports the following list of methods:
Maths
Strings
Dates
Service
If we make a request in all_procedures , we will see more functions, but they are not mentioned in the official documentation.
After the results obtained above, I decided that loading complex packages does not make sense: most likely the problems will be similar and will rest against the lack of certain packages. But there are also simple systems that easily do without DBMS packages and complex functions. I decided to try to assess what problems can be encountered when transferring such systems. For this, I took the Example HR scheme.
She was taken from here:
http://www.oracle.com/technetwork/developer-tools/datamodeler/hr-30-ddl-246035.zip
Execution of this script revealed not full support for Oracle ddl .
Problem number 1
Gives an error message:
In the documentation of the details, how can I ask this I did not find. But judging by the tests, it works in accordance with the value of DEFERRABLE . But with it the nuances (see problem 2)
Problem number 2
Gives an error message:
Again, I did not find the documentation on creating constraints , but in practice, if we want pending constraints, then we write DEFERRABLE , but if we want to always check this constraint, then we do not specify anything (this is the default value).
The scheme has been created; now you can run the scripts to test the capabilities of the DBMS. I took the scripts from here:
http://www.srikanthtechnologies.com/oracle/dec9/hrqueries.html
Problem number 3
Request:
Gives an error:
Correct for Tibero was this request:
This trifle seems to me a problem due to the fact that the company claims that one of the main directions of their development is compatibility with Oracle . Compatibility is not only the support of high-quality code, but also the correct operation of bad code. And when something by default works in Oracle , then with such a policy it is logical to expect it to work by default in Tibero .
I could not get any other errors from these scripts. Separately, I checked pivot, various join notations, hierarchical queries: everything works, pipeline functions work strangely and everything connected with them. Here is a simple example:
Problem number 4
I create types
I create function the issuing table
I make a request:
Received:
I tried to correct the situation:
The result is even worse:
But this manufacturer is afraid to fix it, but for now we have what we have.
Problem number 5
Connecting database managers. This is a whole problem and dancing with tambourines. Most do not want to communicate with the base or the setting is not very obvious. The manufacturer promises to be about to be tbrliboci.dll , but so far it is not.
Problem number 6
I tried Oracle Forms 9 to connect to Tibero . Got a checksum error when connecting. It was not possible to advance further.
Problem number 7
It turned out to be quite painful to use Explain Plan without dbms_xplan.display .
According to the results of tests, downloads of finished packages and some ready-made PL / SQL systems, I can say that quite simple and lightweight systems are well tolerated. Everything works, no problems arise. To write on it from 0 is possible and, probably, quite successfully. Unless, of course, you know English and are willing to endure that something works on something like in Oracle . In the case of large systems, such as an online store, an abs of a bank, warehouse accounting system, problems with packages, poor work with the pipeline make the migration process painful and associated with rewriting the finished code, which is not very pleasant and sometimes quite expensive.
In general, it seems to me that this product has a chance of survival. And now, as I said in the first part, there are Russian companies that work with him. Our guys work there, but there is no sensible information in Russian. Therefore, I really want to start creating a Russian-speaking community of people around this product who can suggest, help or share experience with this sub Tibero .
PS Many thanks to Peter Zhitnikov for help in writing.
To test the compatibility of Tibero with Oracle, I decided to check whether my favorite PL / SQL libraries would work on it, namely:
https://github.com/mortenbra/alexandria-plsql-utils/
Testing began with JSON_UTIL_PKG . A very useful feature that allows you to quickly generate a JSON response.
and so we do
wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/2213e3df2f24a00322239d442c54bf31039f2588/setup/types.sql wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/master/ora/json_util_pkg.pks wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/master/ora/json_util_pkg.pkb To simplify the life of the beginning of the pkb file, add
set define off types.sql installed successfully.
When installing the package, we unexpectedly stumble upon problems. If in oracle the installation of these three files went smoothly with getting the compiled package, then in the case of tibero when filling the package body I received the following conclusion:
tbsql sys/syspass @json_util_pkg.pkb tbSQL 5 SP1 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Connected to Tibero. Warning: PACKAGE BODY created with compilation errors. File finished. Calling
show errors I get an error
TBR-15046: Identifier is out of scope. at line 251, column 5: dbms_xmlgen It turns out that there is no dbms_xmlgen package in tibero . Faced with this problem, I decided to see what kind of DBMS% packages are. Request
select distinct name from all_source where name like 'DBMS%' order by 1 Got the following list:
| DBMS_APM | DBMS_APM_INTERNAL | DBMS_APPLICATION_INFO | DBMS_CRYPTO |
| DBMS_DEBUG | DBMS_DEBUG_JDWP | DBMS_ERRLOG | DBMS_EXPRESSION |
| DBMS_FLASHBACK | DBMS_GEOM | DBMS_JAVA | DBMS_JOB |
| DBMS_JOB_WITH_NAME | DBMS_JOB_WITH_NAME_INTERNAL | DBMS_LOB | DBMS_LOCK |
| DBMS_METADATA | DBMS_MVIEW | DBMS_MVIEW_REFRESH_UTIL | DBMS_MVIEW_UTIL |
| DBMS_OBFUSCATION_TOOLKIT | DBMS_OUTPUT | DBMS_PIPE | DBMS_RANDOM |
| DBMS_REDEFINITION | DBMS_REDEFINITION_STATS | DBMS_REPAIR | DBMS_RESULT_CACHE |
| DBMS_ROWID | DBMS_SESSION | DBMS_SPACE | DBMS_SPACE_ADMIN |
| DBMS_SPH | DBMS_SPH_INTERNAL | DBMS_SQL | DBMS_STATS |
| DBMS_STATS_INTERNAL | DBMS_STATS_UTIL | DBMS_SYSTEM | DBMS_TRANSACTION |
| DBMS_TYPES | DBMS_UTILITY | DBMS_VERIFY | DBMS_XMLGEN |
| DBMS_XPLAN |
Total 45 vs. 195 in my version of oracle . It looks sad. From those packages that I had to use in Oracle , the lack of DBMS_SCHEDULER , DBMS_XMLDOM , etc. upsets.
I think that many in the list of 150 who did not get into tibero dbms will find packages that are not enough for them.
But what is most interesting: the dbms_xmlgen package, due to which there was an error, is present in Tibero . The problem turned out to be that there is no setmaxrows function in the package.
By making
desc dbms_xmlgen Got such a content package in Tibero
| FUNCTION CONVERT (XMLDATA IN VARCHAR, FLAG IN NUMBER) RETURNS VARCHAR |
| FUNCTION CONVERT (XMLDATA IN CLOB, FLAG IN NUMBER) RETURNS CLOB |
| FUNCTION NEWCONTEXT (QUERY IN VARCHAR) RETURNS NUMBER |
| FUNCTION NEWCONTEXT (QUERY IN REF CURSOR) RETURNS NUMBER |
| PROCEDURE SETNULLHANDLING (CTX IN NUMBER, FLAG IN NUMBER) |
| PROCEDURE CLOSECONTEXT (CTX IN NUMBER) |
| FUNCTION GETXML (CTX IN NUMBER, DTDORSCHEMA IN NUMBER) RETURNS CLOB |
| FUNCTION GETXML (SQLQUERY IN VARCHAR, DTDORSCHEMA IN NUMBER) IN RETURNS CLOB |
| PROCEDURE GETXML (CTX IN NUMBER, TMPCLOB IN OUT CLOB, DTDORSCHEMA IN NUMBER) |
As can be seen from the above, Tibero has fewer not only packages, but also procedures inside them. It turns out that most likely the migration of the finished Oracle product to Tibero will be quite difficult, because there is a great chance that packages using Oracle standard packages are not compiled and have to be rewritten, or you can independently implement standard Oracle packages.
')
Having received such a result, I decided to study two more packages in more detail: DBMS_LOB and DBMS_LOCK . They were chosen because they seem to me the most popular among PL / SQL developers.
DBMS_LOB.
The comparison showed that all the basic functions are present, but it is impossible to work with fragments in Tibero , c DBFS_LINK also, and some convenient methods either. Speaking dryly, the DBMS_LOB package in tibero does not contain the following methods:
| COPY_DBFS_LINK | COPY_FROM_DBFS_LINK | DBFS_LINK_GENERATE_PATH | FRAGMENT_DELETE |
| FRAGMENT_INSERT | FRAGMENT_MOVE | FRAGMENT_REPLACE | Getchunksize |
| Getcontienttype | Getoptions | GET_DBFS_LINK | GET_DBFS_LINK_STATE |
| GET_DEDUPLICATE_REGIONS | GET_STORAGE_LIMIT | ISSECUREFILE | LOADBLOBFROMFILE |
| LOADCLOBFROMFILE | LOADFROMFILE | MOVE_TO_DBFS_LINK | SETCONTENTTYPE |
| SETOPTIONS | SET_DBFS_LINK |
DBMS_LOCK is fully implemented.
With UTL_% packets, the situation is close to DBMS_% . There are 11 packages in total at Tibero . Of which 8 ( UTL_ENCODE , UTL_FILE , UTL_HTTP , UTL_I18N , UTL_MATCH , UTL_RAW , UTL_TCP , UTL_URL ) are similar to standard Oracle packages, the remaining three are their own. Oracle, in turn, has packages of 21, thus the following 13 packages are not implemented in Tibero :
| UTL_CALL_STACK | UTL_COLL | UTL_COMPRESS | UTL_GDK |
| UTL_IDENT | UTL_INADDR | UTL_LMS | UTL_NLA |
| UTL_RECOMP | UTL_REF | UTL_SMTP | UTL_SYS_COMPRESS |
| UTL_XML |
I wonder why Tibero doesn't like XML ...
The state of the libraries is not very optimistic. But not always we use DBMS and UTL packages! Sometimes there are enough standard functions, and with them everything is more or less optimistic. TMax actively expands the list of supported functions and now stably supports the following list of methods:
Maths
| ABS | ACOS | ASIN | ATAN | ATAN2 |
| Avg | Bitand | CEIL | Cos | Cosh |
| COUNT | EXP | FLOOR | Ln | LOG (M, N) |
| MOD | Power | ROUND | SIGN | Sin |
| SINH | Sqrt | Tan | TANH | TRUNC |
Strings
| CONCAT | CHR | LOWER | UPPER | LPAD |
| LTRIM | Rtrim | RPAD | SUBSTR | REPLACE |
| INITCAP | NLS_INITCAP | NLS_SORT | TRANSLATE | TO_CHAR |
| ASCII | INSTR | LENGTH | LENGTHB |
Dates
| + integer | -date | ADD_MONTHS | CURRENT_DATE |
| CURRENT_TIME | CURRENT_TIMESTAMP | LAST_DAY | NEW_TIME |
| NEXT_DAY | ROUND | TRUNC | EXTRACT |
| SYSTIMESTAMP | MONTHS_BETWEEN | TO_CHAR | TO_NUMBER |
| TO_DATE | TO_DSINTERVAL | SYSDATE |
Service
| GREATEST | LEAST | CAST | CONVERT |
| HEXTOCHAR | ROWIDTOCHAR | TO_CLOB | TO_LOB |
| DECODE | DUMP | VSIZE | COALEASE |
| Lnnvl | NULLIF | NVL | NVL2 |
If we make a request in all_procedures , we will see more functions, but they are not mentioned in the official documentation.
After the results obtained above, I decided that loading complex packages does not make sense: most likely the problems will be similar and will rest against the lack of certain packages. But there are also simple systems that easily do without DBMS packages and complex functions. I decided to try to assess what problems can be encountered when transferring such systems. For this, I took the Example HR scheme.
She was taken from here:
http://www.oracle.com/technetwork/developer-tools/datamodeler/hr-30-ddl-246035.zip
Execution of this script revealed not full support for Oracle ddl .
Problem number 1
ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_DATE_CHECK CHECK (end_date > start_date) INITIALLY IMMEDIATE ENABLE VALIDATE ; Gives an error message:
TBR-7001: General syntax error. at line 4, column 9: INITIALLY IMMEDIATE In the documentation of the details, how can I ask this I did not find. But judging by the tests, it works in accordance with the value of DEFERRABLE . But with it the nuances (see problem 2)
Problem number 2
ALTER TABLE COUNTRIES ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY ( REGION_ID ) REFERENCES REGIONS ( REGION_ID ) NOT DEFERRABLE ; Gives an error message:
TBR-7001: General syntax error. at line 10, column 5: NOT DEFERRABLE Again, I did not find the documentation on creating constraints , but in practice, if we want pending constraints, then we write DEFERRABLE , but if we want to always check this constraint, then we do not specify anything (this is the default value).
The scheme has been created; now you can run the scripts to test the capabilities of the DBMS. I took the scripts from here:
http://www.srikanthtechnologies.com/oracle/dec9/hrqueries.html
Problem number 3
Request:
SELECT SYSDATE - to_date('01-jan-2011') FROM DUAL Gives an error:
TBR-5113: Invalid month value 'jan-2011'. (01-jan-2011) Correct for Tibero was this request:
SELECT SYSDATE - to_date('20110101') from dual This trifle seems to me a problem due to the fact that the company claims that one of the main directions of their development is compatibility with Oracle . Compatibility is not only the support of high-quality code, but also the correct operation of bad code. And when something by default works in Oracle , then with such a policy it is logical to expect it to work by default in Tibero .
I could not get any other errors from these scripts. Separately, I checked pivot, various join notations, hierarchical queries: everything works, pipeline functions work strangely and everything connected with them. Here is a simple example:
Problem number 4
I create types
CREATE OR REPLACE TYPE TEST_TYPE IS OBJECT( TESTYPE NUMBER ); CREATE OR REPLACE TYPE TEST_TYPE_TABLE IS TABLE OF TEST_TYPE; I create function the issuing table
CREATE OR REPLACE FUNCTION TEST_FUNCTION(PARAM IN NUMBER) RETURN TEST_TYPE_TABLE IS RSLT TEST_TYPE_TABLE; BEGIN RSLT := TEST_TYPE_TABLE(); RSLT.EXTEND; RSLT(RSLT.LAST) := TEST_TYPE(PARAM); RETURN RSLT; END; I make a request:
SELECT * FROM TABLE( test_function( 2 ) ) Received:
TBR-8051: User-defined functions are not permitted in the expression. at line 1, column 23: select * from table( test_function(2) ) I tried to correct the situation:
select * from table( cast(test_function(2) as test_type_table) ) The result is even worse:
TBR-8021: Invalid table name. at line 1, column 47: select * from table( cast(test_function(2) as test_type_table) ) But this manufacturer is afraid to fix it, but for now we have what we have.
Problem number 5
Connecting database managers. This is a whole problem and dancing with tambourines. Most do not want to communicate with the base or the setting is not very obvious. The manufacturer promises to be about to be tbrliboci.dll , but so far it is not.
Problem number 6
I tried Oracle Forms 9 to connect to Tibero . Got a checksum error when connecting. It was not possible to advance further.
Problem number 7
It turned out to be quite painful to use Explain Plan without dbms_xplan.display .
According to the results of tests, downloads of finished packages and some ready-made PL / SQL systems, I can say that quite simple and lightweight systems are well tolerated. Everything works, no problems arise. To write on it from 0 is possible and, probably, quite successfully. Unless, of course, you know English and are willing to endure that something works on something like in Oracle . In the case of large systems, such as an online store, an abs of a bank, warehouse accounting system, problems with packages, poor work with the pipeline make the migration process painful and associated with rewriting the finished code, which is not very pleasant and sometimes quite expensive.
In general, it seems to me that this product has a chance of survival. And now, as I said in the first part, there are Russian companies that work with him. Our guys work there, but there is no sensible information in Russian. Therefore, I really want to start creating a Russian-speaking community of people around this product who can suggest, help or share experience with this sub Tibero .
PS Many thanks to Peter Zhitnikov for help in writing.
Source: https://habr.com/ru/post/270395/
All Articles