myimport Problem ✓ Answered: green (solid confidence) — David progressively debugged myexport/myimport on Solaris himself (PATH, GNU sed/awk vs. Solaris versions, sbspace logging for a LONG TRANSACTION), and Art Kagel's diagnosis of stray comment text from CREATE PROCEDURE...FROM FILE fixed most of the remaining SPL import errors, which David confirmed.

Advisory only.

IDS 12.10.FC12 Solaris 10 1/13 I am likely doing something blindingly obviously wrong... I am trying to use Art's "myexport" and "myimport" to speed up the export and import of a database. I tried the following experiment. First, I used myexport to export a database using external tables (took 22 minutes). I did a 'head' on both stdout and stderr. Then (not shown) I DROPped the database. Then, I used myimport to attempt to import the export I just made (took less than 1 minute, which, of course means it failed). I then did a 'head' on both stdout and stderr from the import attempt. The telnet session is shown below (I did some minor editing of white space for clarity): BEGIN TELNET SESSION informix@ifmx-prod-jnu>pwd /arc/export2 informix@ifmx-prod-jnu>ls informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>myexport -ss -E acoms >out.export 2>err.export informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>ls -l total 1495 drwxr-xr-x 2 informix informix 4623 Mar 1 13:01 acoms.exp -rw-r--r-- 1 informix informix 219031 Mar 1 13:01 err.export -rw-r--r-- 1 informix informix 1721 Mar 1 13:01 out.export informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>head out.export -ss is assumed. Building schema file myschema --wrap-at=-1 -q -d acoms -l --myexport-scripts /arc/export2/acoms.exp/acoms.sql Beginning data export Unloading data for table: <snip white space> informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>head err.export "informix".acct_hld_cd "informix".addr_typ_cd "informix".adj_rsn_cd "informix".agcy_loc "informix".agcy_usr_cllct "informix".agcy_usr_dist "informix".agcy_usr_hrs "informix".applc_grp "informix".applc_modl "informix".applc_scrty informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>myimport -E -l nolog -i /arc/export2 acoms >out.import 2>err.import informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>ls -l total 2270 drwxr-xr-x 2 informix informix 4623 Mar 1 14:30 acoms.exp -rw-r--r-- 1 informix informix 219031 Mar 1 13:01 err.export -rw-r--r-- 1 informix informix 372869 Mar 1 14:30 err.import -rw-r--r-- 1 informix informix 1721 Mar 1 13:01 out.export -rw-r--r-- 1 informix informix 171 Mar 1 14:30 out.import informix@ifmx-prod-jnu> informix@ifmx-prod-jnu>head out.import Creating Database: acoms CREATE DATABASE acoms ; Loading schema. Using Schema ==> /arc/export2/acoms.exp/acoms.sql Data loads complete. myimport: Processing complete! informix@ifmx-prod-jnu> informix@ifmx-prod-jnu> head err.import awk: syntax error near line 33 awk: bailing out near line 33 sed: illegal option -- r Database created. Database closed. informix@ifmx-prod-jnu> END TELNET SESSION I'm not 100% sure how to interpret the error messages. Would the complaints about "line 33" refer to line 33 of the schema file? Here are the first 35 lines of the schema file (acoms.sql): ADDITIONAL TELNET informix@ifmx-prod-jnu>head -35 ./acoms.exp/acoms.sql { DATABASE acoms delimiter | } CREATE ROLE "ReadOnly"; CREATE ROLE "datafix"; GRANT DBA TO "bjpetal"; GRANT DBA TO "pdamerva"; GRANT DBA TO "informix"; GRANT DBA TO "degrove"; GRANT DBA TO "lwharton"; GRANT DBA TO "mdgimm"; GRANT DBA TO "dba"; GRANT CONNECT TO "dpsfprt"; GRANT CONNECT TO "ecc"; GRANT CONNECT TO "public"; GRANT "datafix" TO "mlopez"; GRANT "datafix" TO "pcrandel"; GRANT SETSESSIONAUTH ON "public" TO "lwharton"; GRANT usage ON LANGUAGE spl TO "public"; CREATE OPAQUE TYPE 'informix'.sysbldsqltext ( internallength=variable, maxlen=24000, alignment=1 ); GRANT USAGE ON TYPE 'informix'.sysbldsqltext TO 'public' AS 'informix'; CREATE ROW TYPE 'informix'.bts_fsestoragerow ( sto_name "informix".lvarchar(2048), num_buckets INTEGER, bucket_num INTEGER, informix@ifmx-prod-jnu> END ADDITIONAL TELNET What am I doing wrong? Thank you. DG

I just realized that I have failed to include "from_external.awk" and "to_external.awk" in the PATH. I added them to the PATH (after making sure they both had ugo+x permissions. Now, now explicit error message, but "myexport" terminates within seconds, and produces zero .unl files. DG

Disregard my immediately preceding, inept post. After starting over, "myexport" runs properly. That is, it produces a proper unl file for every table in the database. But, I still cannot get myimport to execute. It ripples through every table and reports that the table does not exist in the database. That is, I run 'myimport -E -l nolog -t /arc/export2 acoms >out.myimport 2>err.myimport'. But, every table is reported as not existing. Here is the result of running 'head -40 err.myimport' (with some whitespace deleted for clarity.) informix@ifmx-prod-jnu>head -40 err.myimport awk: syntax error near line 33 awk: bailing out near line 33 sed: illegal option -- r Database created. Database closed. Database selected. Database closed. Database selected. Environment set. Table created. 206: The specified table (acct_hld_cd) is not in the database. 111: ISAM error: no record found. Error in line 9Near character position 4 Table dropped. Database closed. Any suggestions are welcome. Thank you. DG

I concluded that the 3rd line of the error messages from myimport's stderr, "sed: illegal option -- r" came from the Solaris version of sed, and that "myimport" must need the GNU version. So, I edited the "myimport" script to refer to "gsed", instead of "sed". That eliminated the "sed" error message. But, the first two lines of error messages: awk: syntax error near line 33 awk: bailing out near line 33 remain. Now, when I run 'myimport -E -l nolog -i <path/to/the/.exp> <db>', all the error messages about nonexistent tables go away. These are the ones that disappeared: 206: The specified table (acct_hld_cd) is not in the database. 111: ISAM error: no record found. Now, most of the stored procedures seem to import. But, not all. A few generate syntax error messages. This I don't understand, since the source is the schema file generated by myexport. Also, some of the tables now import, but, some do not. All of them are properly defined in the database, but some have their data, and some do not (zero rows). I wonder how much of this derives from the awk error messages at the top of this post. What awk file do these error messages refer to? "myimport" itself? "from_external.awk"? Something else I am completely missing? I think I'm still a long way from having 'myimport' just work. The potential speed advantage of getting this to work is very valuable. But, it just doesn't seem to be happening. DG

In relation to the awk errors, could it be the same issue you had with sed? Expecting gnu awk, but using Solaris awk ? Luis Marques

Thank you for the suggestion. I examined the "myimport" script, and observed that there was some code to determine what version of awk was available, and the result of this was used to set an environment variable that was then used later to actually invoke awk. Except that it appeared that there was one other invocation of awk that had "awk", instead of the script variable, hardwired into the script (thus invoking the Solaris /usr/bin/awk which is in the PATH). I don't know if changing that reference so that it will use the GNU awk will help or not, but I am trying it. DG

(See my immediately previous post) Changing the 'myimport' script to eliminate the hardwired reference to "awk", eliminated the awk errors I described earlier. Now, the only errors that remain are syntax errors during the execution of myimport. That is, the result of running myexport produces files that result in syntax errors when executing myimport to attempt to import the database just exported. I need to investigate this further, but after a very quick look, it seems that there may be issues with statements in SPLs that are commented out, but somehow generating syntax errors during the import. I'll continue to check this out, as I have time. DG P.S. In the past, using the official dbimport and dbexport, i have also encountered situations in which dbimport cannot import the product of dbexport. I first encountered this about 15 years ago, and have always wondered why IBM didn't fix it.

Recap: The two errors originally at the top of the stderr output from myimport are now gone, after making sure that the gnu versions of sed and awk were being used. I am still working on several remaining syntax errors that appear to derive from comments in the SPLs that are not accurately recognized as comments. Anyway, Now that it appears that all the tables are actually (attempting to be) imported, I am starting a separate, parallel effort to try to fix one or two remaining problems with table data imports. I am running into a LONG TRANSACTION error with one of the tables. I have used the "-n nolog" flag to eliminate logging, but, this particular table has a column that is a smart large object (a photo). Creation of smart large objects is always logged and that logging can't be disabled (so far as I know). There are several 100K rows in that table, and the it gets, maybe, 40% loaded before the LONG TRANSACTION abort. Any suggestions on how I can 'myimport' the data into that table? Thank you. DG

Thanks to a boost from Art, this problem is resolved. I admit i should have realized it myself. The answer is turn off logging in the sbspace: 'onspaces -ch <sbspace> -Df "LOGGING=OFF"' . Then, after successful import, turn it back on. The syntax issues that seem caused by something in the chain of myimport processing doesn't quite recognize some comments contained in stored procedures as comments. Apparently tries to parse them and produces an error. Still working on those. DG

To summarize: I have encountered several issues attempting to use myexport/myimport. Thanks to a boost from Art, I have solved one of them. That is the long transaction error. This issue does not appear when using dbexport/dbimport, but does manifest when using myimport. The solution is to disable logging during the import, for the relevant sbspace(s). Then re-enable when the import is complete. I have several other issues that I am still working on. These issues seem to come from something in the myexport/myimport chain misinterpreting comments in SPLs, and creating a schema file with syntax errors. This, of course, causes myimport to fail. Some examples of these are: 1) Quoted strings in an SPL are sometimes split into multiple lines in the schema file, but the newlines are not escaped, thus producing "mismatched quote" error messages during import; 2) Duplicate "CREATE PROCEDURE..." statements for a few SPLs, thus causing syntax errors during import; 3) Omission of "END" in some CREATE FUNCTION statements, thus causing syntax error during import; 4) Some Duplicate AGGREGATE error messages that I don't yet fully understand; 5) Multiple errors that seem to come from BTS blade, that I don't yet fully understand. I confident that I am the cause of these problems, and that I have some peculiarity in my own installation. I'm going to continue to work on these issues for a while because the speed advantage of myexport/myimport will be extremely valuable to us if I can get these utilities working. To avoid cluttering this thread with discussion of multiple errors, I will post results (if and when I resolve them) in separate threads, each of which focuses on a single issue. DG

David: That is usually because the procedures were created with the FROM FILE option and the file contained comments and other text outside of the procedure text itself. Informix is smart enough to ignore that text during a FROM FILE create but not during an ordinary SQL session. Unfortunately though it ignores the extraneous text it does include it in the procedure's source code which myschema diligently prints out into the schema file. And that text is what causes the errors trying to create the procs. I have a client that hits this one all the time and I'm having to migrate their servers to a new platform now and dealing with it daily. <sigh> If that is not what you are seeing then send me the schema file and an unload from your sysprocedures and sysprocbody tables and I will try to fix the problem.

Thank you, Art. Your intuition was spot on. Although the SPLs in question didn't originate via CREATE PFROCEDURE FROM..., they were due to comments placed "outside the box" so to speak, via an SQL Command window in Server Studio. I manually went through the 10, or so, SPLs, and either removed or relocated the offending comments. This cleared up the majority of the problems. I encountered another problem, and found a work-around. But, maybe Art has a better answer. The 2nd schema file (<database>2.sql) in the results of 'myexport -ss -E -m <database>' contains several CREATE AGGREGATE statements. (Note that dbexport does not produce any of these.) All of them produce an error when myimport tries to import them. The error is that (in each case) the aggregate already exists. My work-around is to edit the schema file before running myimport and simply delete all the CREATE AGGREGATE statements. When I do this, there are no duplicate aggregate error messages, and all the aggregates are present. Is there a way to have myexport just not create the (apparently redundant) CREATE AGGREGATE statements? Thank you. DG

Resolutions of issues summarized in previous post: [1) Quoted strings in an SPL are sometimes split into multiple lines in the schema file, but the newlines are not escaped, thus producing "mismatched quote" error messages during import;] Resolution: Parsing issue related to distinguishing actual keywords from text that happens to have a sequence of characters that would be a keyword if not in a text string. Code is being revised. [2) Duplicate "CREATE PROCEDURE..." statements for a few SPLs, thus causing syntax errors during import;] Resolution: Parsing issue related to comments being present in unexpected places. Code is being revised. [3) Omission of "END" in some CREATE FUNCTION statements, thus causing syntax error during import;] Resolution: My ignorance. "END FUNCTION" not necessary in code that is not an SPL. That is, code that is an external "C" function. [4) Some Duplicate AGGREGATE error messages that I don't yet fully understand;] Resolution: Aggregates from extended Informix (a blade). Work around is to DELETE all the CREATE AGGREGATE statements that are not user-created. [5) Multiple errors that seem to come from BTS blade, that I don't yet fully understand.] Resolution: Not fully resolved, yet. I still don't really understand this issue. Since the blade functionality is not required for this particular database, I simply eliminated all blades. I used blademgr to 'unregister' all (there were two) of them. So, now I have zero blades in this database. Yet, I continue to have a blade related problem, I think. I will start a new thread to deal with this remaining issue that, somehow, even though there are no blades, still seems to be related to blades. The schema file from myexport still is trying to build the sysbldobjects table, and that table requires the sysbldsqltext data type. This generates an error because there are no blades and thus (as far as I can tell), no sysbldsqltext data type. So, when myimport tries to execute the CREATE TABLE statement for the sysbldobjects table, I get a -9628 error (identifying the culprit as undefined type (sysbldsqltext)). Since this thread is already a mishmash of various issues, so I'll start a new thread just for this issue. Thank you. DG

P.S. I should have mentioned that, regarding issues 1) and 2), the SPLs are easily edited to prevent the problems from occurring during the import.