Executing Informix dbaccess in a Windows background environment

Our main objective is to execute an Informix stored procedure that will export daily data from selected date-dependant database tables to disc files in a specified folder. This should be done in a Windows environment. We have tested a prototype procedure using a 'System' call to create an sql script file each day, and to execute it using dbaccess. (We used this method because SPL does not support the UNLOAD function. We keep getting error -668, which seems to mean that the user does not have permission to create the output file (which is strange because it has no trouble creating a trace file). The explanation for error -668 is: 1) Make sure that the system command is not trying to run something that is not in your path or that is not applicable to the environment. 2) Make sure that you have provided a user-id and password when connecting to the database server. The database server must have the user's password in order to execute a command on behalf of that user. 3) On Windows platforms, make sure the following Advanced User Rights have been assigned to user "informix": a) Act as part of the operating system; b) Increase quotas; c) replace a process level token Conditions 1 & 2 were fulfilled, but no amount of fiddling with ownership and access rights on the folders helped, although we have not consciously implemented conditions 3a, b & c. Please help, as I am not familiar with this part of Windows. Up until now our tests have been carried out interactively, and entering the password is not a problem. To accomplish this automatically, we tried to run a '.bat' file in the style of the following Unix script, hoping to connect to the database in non-menu mode: dbaccess - - <<\\! connect to 'dcs_ctr' user 'informix' 'pw01' q c 'exp' r e e ! In the above script, 'pw01' is the hard-coded user password, and 'exp' refers to an sql script, 'exp.sql', that uses the 'unload' function to extract the data from the database. The single letters in the script activate dbaccess menu commands. Upon invoking this script an error was returned in the first line indicating that '<<' was unexpected. In Unix, '<<!' means "take input from the lines that follow in the script up to a line containing '!' ". Please help me establish the Windows equivalent of this instruction. Thanks David Reed

Hello David, There is an example of equivalent UNIX and Windows dbaccess scripts here: http://www-03.ibm.com/developerworks/blogs/page/gbowerman?entry=scripting_dbaccess I don't understand why you need to call a dbaccess script from a stored procedure and not execute the SQL commands from SPL - but maybe there are additional OS commands you need to execute in the script. If you need to execute a batch file from an SP SYSTEM command, as well as logging on with a user and password (which you are doing), you'll probably need to prefix the file with cmd /c, e.g. SYSTEM "cmd /c c:\\temp\\myfile.bat" Regards Guy David Reed wrote: > Our main objective is to execute an Informix stored procedure that will > export daily data from selected date-dependant database tables to disc > files in a specified folder. This should be done in a Windows > environment. We have tested a prototype procedure using a 'System' call > to create an sql script file each day, and to execute it using dbaccess. > (We used this method because SPL does not support the UNLOAD function. > We keep getting error -668, which seems to mean that the user does not > have permission to create the output file (which is strange because it > has no trouble creating a trace file). The explanation for error -668 is: > > > > 1) Make sure that the system command is not trying to run > something that is not in your path or that is not applicable to the > environment. > > 2) Make sure that you have provided a user-id and password when > connecting to the database server. The database server must have the > user's password in order to execute a command on behalf of that user. > > 3) On Windows platforms, make sure the following Advanced User > Rights have been assigned to user "informix": > > a) Act as part of the operating system; > > b) Increase quotas; > > c) replace a process level token > > > > Conditions 1 & 2 were fulfilled, but no amount of fiddling with > ownership and access rights on the folders helped, although we have not > consciously implemented conditions 3a, b & c. Please help, as I am not > familiar with this part of Windows. > > > > Up until now our tests have been carried out interactively, and entering > the password is not a problem. To accomplish this automatically, we > tried to run a '.bat' file in the style of the following Unix script, > hoping to connect to the database in non-menu mode: > > > > dbaccess - - <<\\! > > connect to 'dcs_ctr' user 'informix' > > 'pw01' > > q > > c > > 'exp' > > r > > e > > e > > ! > > > > > > In the above script, 'pw01' is the hard-coded user password, and 'exp' > refers to an sql script, 'exp.sql', that uses the 'unload' function to > extract the data from the database. The single letters in the script > activate dbaccess menu commands. > > > > Upon invoking this script an error was returned in the first line > indicating that '<<' was unexpected. In Unix, '<<!' means 'take input > from the lines that follow in the script up to a line containing '!' '. > Please help me establish the Windows equivalent of this instruction. > > > > > > */Thanks/* > > */David Reed/* >