unload to file include date
User wanted to generate dated unload filenames (e.g., xyz20121115.unl) directly in dbaccess SQL. Pure SQL cannot do this, but the solution is to wrap dbaccess in a shell script using date command substitution: dbaccess mydatabase - <<EOF\\nunload to xyz.$(date +%Y%m%d).unl\\nselect * from xyz;\\nEOF. User accepted this approach for selective table backups.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a IDS 11.70 running in RedHat 5.5 , the following sql is what i do
in dbaccess everyday to backup :
unload to xyz.unl
select * from xyz ;
however , I like to backup not just xyz.unl ,
for example , today is 20121115 , then it would be perfect
to xyz20121115.unl , and tomorrow to be xyz20121116.unl
something like :
unload to xyz%today%.unl
select * from xyz ;
it is easy to an esql/c application , i think it is easy in spl ,
but can it be achieved in just pure dbaccess sql command ?
Thanks for any suggestions !!
Oh... I am wrong , spl not quite easy , esql/c should be quite easy though ~~
No, but you can do it easily in a ksh/bash script:
dbaccess mydatabase - <<EOF
unload to xyz.$(date +%Y%m%d).unl
select * from xyz;EOF
You can even easily script the tablenames:
dbaccess mydatabase - <<EOF
unload to table.lst delimiter ' '
select tabname from systables where tabid > 100;EOF
for table in $(cat table.lst); do
dbaccess mydatabase - <<EOF
unload to $table.$(date +%Y%m%d).unl
select * from $table;EOF
Now I have to ask, why not just use ontape to archive your server?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Nov 14, 2012 at 8:26 PM, MARS CHEN <hedgezzz@yahoo.com.tw> wrote:
> I have a IDS 11.70 running in RedHat 5.5 , the following sql is what i do
> in dbaccess everyday to backup :
>
> unload to xyz.unl
> select * from xyz ;>
> however , I like to backup not just xyz.unl ,
> for example , today is 20121115 , then it would be perfect
> to xyz20121115.unl , and tomorrow to be xyz20121116.unl
>
> something like :
>
> unload to xyz%today%.unl
> select * from xyz ;>
> it is easy to an esql/c application , i think it is easy in spl ,
> but can it be achieved in just pure dbaccess sql command ?
>
> Thanks for any suggestions !!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9399c71d5241004ce8096aa
I think you could do a substring select from 'select sysdate from sysdual' to
get the date portion. You could then use that as a portion of the file name if
you wish to do it in dbaccess I believe.
On Wed, Nov 14, 2012 at 6:09 PM, MARS CHEN <hedgezzz@yahoo.com.tw> wrote: > Oh... I am wrong , spl not quite easy , > esql/c should be quite easy though ~~ > ESQL/C is easy enough after you've written the code to do UNLOAD. That's a non-trivial exercise. The file name is the least of your problems... Don't forget, that UNLOAD and LOAD are pseudo-SQL statements. DB-Access, ISQL and I4GL make it look like an ordinary SQL statement, but it is simulated in the client code; the server has no built-in support for UNLOAD (though an external table gets fairly close). -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04088d11e76a7204ce8b0b70
Thanks you for your kindly shell information !!
The reason I do not use ontape is because the table I
need to backup is just 3 of all , others is not important ,
so backup these 3 tables everyday would be enough ...
Thanks again !!