Unload to and ODBC

Hello, I need to make backup of my prime database while its running, prohibiting the use of dbexport. When using dbaccess i can do an unload to 'myfile' select * from mytable without any problem. As i have a large amount of tables i thought to make a little software to backup all the tables i needed to save using ODBC ... here is the problem, the same request which work on dbaccess does not work using sqlview connected by odbc. it keep on returning "syntax error" My database is IDS 7.31 and my system is Win NT 2000 server. Any help would be great. Thanks in advance, Fred

Bob wrote: > > As i have a large amount of tables i thought to make a little software to > backup all the tables i needed to save using ODBC ... here is the problem, > the same request which work on dbaccess does not work using sqlview > connected by odbc. it keep on returning "syntax error" > > That is because 'unload' to is a dbaccess extension and not part of the sql syntax You will have to roll your own if you want to do it over ODBC

That is the answer i expected :-( many thanks anyway, will have to go back to my own backup procedure :-) Thanks again. Fred "Clive Eisen" <clive@serendipita.com> a 'crit dans le message de news: 4208a131$0$4097$db0fefd9@news.zen.co.uk... > Bob wrote: >> >> As i have a large amount of tables i thought to make a little software to >> backup all the tables i needed to save using ODBC ... here is the >> problem, the same request which work on dbaccess does not work using >> sqlview connected by odbc. it keep on returning "syntax error" >> > >> > That is because 'unload' to is a dbaccess extension and not part of the > sql syntax > > You will have to roll your own if you want to do it over ODBC

Why not use ontape or onbar to take an backup while the system is running? If you continue allowing access to the data while you make the ASCII archive then you will potentially have referential consistency problems if you need to restore. eg someone removes a customer and all their orders, at the time you perform your unload of the orders table the orders exist but by the time you are performing the unload of the customer table the customer has gone. When you retore this data you will have orders for which a customer does not exist. Bob wrote: > Hello, > > I need to make backup of my prime database while its running, prohibiting > the use of dbexport. > > When using dbaccess i can do an unload to 'myfile' select * from mytable > without any problem. > > As i have a large amount of tables i thought to make a little software to > backup all the tables i needed to save using ODBC ... here is the problem, > the same request which work on dbaccess does not work using sqlview > connected by odbc. it keep on returning "syntax error" > > My database is IDS 7.31 and my system is Win NT 2000 server. > > Any help would be great. > > Thanks in advance, > > Fred

I know about this problem but in fact my need is to make a partial export of about 80% of the tables in the base each night to import them in another database. I already have some process extracting data between 8h00pm and 10h00pm and the partial backup must take place after this process. Right now i use an home made process, i list all the tables i need to backup, execute a select * from mytable for all the tables selected and save row after row in ascii files then i import them back in the other database. At the time i do the transfert nobody is using the database so referential consistency is not a problem. I just wanted to optimize the process and the unload was faster than my homemade backup :-) Fred. "scottishpoet" <dryburghj@yahoo.com> a 'crit dans le message de news: 1107869112.487342.62520@c13g2000cwb.googlegroups.com... > Why not use ontape or onbar to take an backup while the system is > running? > > If you continue allowing access to the data while you make the ASCII > archive then you will potentially have referential consistency problems > if you need to restore. > > eg someone removes a customer and all their orders, at the time you > perform your unload of the orders table the orders exist but by the > time you are performing the unload of the customer table the customer > has gone. > > When you retore this data you will have orders for which a customer > does not exist. > > > > > > Bob wrote: >> Hello, >> >> I need to make backup of my prime database while its running, > prohibiting >> the use of dbexport. >> >> When using dbaccess i can do an unload to 'myfile' select * from > mytable >> without any problem. >> >> As i have a large amount of tables i thought to make a little > software to >> backup all the tables i needed to save using ODBC ... here is the > problem, >> the same request which work on dbaccess does not work using sqlview >> connected by odbc. it keep on returning "syntax error" >> >> My database is IDS 7.31 and my system is Win NT 2000 server. >> >> Any help would be great. >> >> Thanks in advance, >> >> Fred >

Whats the Other database? Is it also IDS? is it MS Access SQL Server, Oracle...??? Bob wrote: > I know about this problem but in fact my need is to make a partial export of > about 80% of the tables in the base each night to import them in another > database. I already have some process extracting data between 8h00pm and > 10h00pm and the partial backup must take place after this process. > > Right now i use an home made process, i list all the tables i need to > backup, execute a select * from mytable for all the tables selected and save > row after row in ascii files then i import them back in the other database. > > At the time i do the transfert nobody is using the database so referential > consistency is not a problem. > > I just wanted to optimize the process and the unload was faster than my > homemade backup :-) > > Fred. > > > "scottishpoet" <dryburghj@yahoo.com> a écrit dans le message de news: > 1107869112.487342.62520@c13g2000cwb.googlegroups.com... > > Why not use ontape or onbar to take an backup while the system is > > running? > > > > If you continue allowing access to the data while you make the ASCII > > archive then you will potentially have referential consistency problems > > if you need to restore. > > > > eg someone removes a customer and all their orders, at the time you > > perform your unload of the orders table the orders exist but by the > > time you are performing the unload of the customer table the customer > > has gone. > > > > When you retore this data you will have orders for which a customer > > does not exist. > > > > > > > > > > > > Bob wrote: > >> Hello, > >> > >> I need to make backup of my prime database while its running, > > prohibiting > >> the use of dbexport. > >> > >> When using dbaccess i can do an unload to 'myfile' select * from > > mytable > >> without any problem. > >> > >> As i have a large amount of tables i thought to make a little > > software to > >> backup all the tables i needed to save using ODBC ... here is the > > problem, > >> the same request which work on dbaccess does not work using sqlview > >> connected by odbc. it keep on returning "syntax error" > >> > >> My database is IDS 7.31 and my system is Win NT 2000 server. > >> > >> Any help would be great. > >> > >> Thanks in advance, > >> > >> Fred > >

Its the same IDS 7.31 with another database with differents informations in it, we use it to build reports. Fred "scottishpoet" <dryburghj@yahoo.com> a 'crit dans le message de news: 1107873070.579704.119530@o13g2000cwo.googlegroups.com... Whats the Other database? Is it also IDS? is it MS Access SQL Server, Oracle...??? Bob wrote: > I know about this problem but in fact my need is to make a partial export of > about 80% of the tables in the base each night to import them in another > database. I already have some process extracting data between 8h00pm and > 10h00pm and the partial backup must take place after this process. > > Right now i use an home made process, i list all the tables i need to > backup, execute a select * from mytable for all the tables selected and save > row after row in ascii files then i import them back in the other database. > > At the time i do the transfert nobody is using the database so referential > consistency is not a problem. > > I just wanted to optimize the process and the unload was faster than my > homemade backup :-) > > Fred. > > > "scottishpoet" <dryburghj@yahoo.com> a 'crit dans le message de news: > 1107869112.487342.62520@c13g2000cwb.googlegroups.com... > > Why not use ontape or onbar to take an backup while the system is > > running? > > > > If you continue allowing access to the data while you make the ASCII > > archive then you will potentially have referential consistency problems > > if you need to restore. > > > > eg someone removes a customer and all their orders, at the time you > > perform your unload of the orders table the orders exist but by the > > time you are performing the unload of the customer table the customer > > has gone. > > > > When you retore this data you will have orders for which a customer > > does not exist. > > > > > > > > > > > > Bob wrote: > >> Hello, > >> > >> I need to make backup of my prime database while its running, > > prohibiting > >> the use of dbexport. > >> > >> When using dbaccess i can do an unload to 'myfile' select * from > > mytable > >> without any problem. > >> > >> As i have a large amount of tables i thought to make a little > > software to > >> backup all the tables i needed to save using ODBC ... here is the > > problem, > >> the same request which work on dbaccess does not work using sqlview > >> connected by odbc. it keep on returning "syntax error" > >> > >> My database is IDS 7.31 and my system is Win NT 2000 server. > >> > >> Any help would be great. > >> > >> Thanks in advance, > >> > >> Fred > >

Aubit4GL has a windows build - it also works via ODBC and implements the Unload command... If you have the ClientSDK - you could also use native connections (via ESQL/C. If you have esql/c another option is the aubit4gl 'asql' program - it's a sort of dbaccess clone (still a work in progress -especially on windows, but for your needs would probably be ideal..) If none of those appeal - I also have a simple C program to do the specific task of an ODBC unload - email me if you want details... Bob wrote: > Hello, > > I need to make backup of my prime database while its running, prohibiting > the use of dbexport. > > When using dbaccess i can do an unload to 'myfile' select * from mytable > without any problem. > > As i have a large amount of tables i thought to make a little software to > backup all the tables i needed to save using ODBC ... here is the problem, > the same request which work on dbaccess does not work using sqlview > connected by odbc. it keep on returning "syntax error" > > My database is IDS 7.31 and my system is Win NT 2000 server. > > Any help would be great. > > Thanks in advance, > > Fred

why not write some SPL that copies the data directly from the one isnatnce into the other?

Bob wrote: > Hello, Get my dbexport/dbimport replacement utility, myexport. It does not lock the tables/database. There are several extensions, improvements including using onpload for exporting and exporting or importing in parallel to speed things up and reduce intra-table data inconsistencies when exporting a 'live' database. You need the following packages from the IIUG Software Repository: My myexport and utils2_ak packages and Jonathan Leffler's sqlcmd package. Art S. Kagel > I need to make backup of my prime database while its running, prohibiting > the use of dbexport. > > When using dbaccess i can do an unload to 'myfile' select * from mytable > without any problem. > > As i have a large amount of tables i thought to make a little software to > backup all the tables i needed to save using ODBC ... here is the problem, > the same request which work on dbaccess does not work using sqlview > connected by odbc. it keep on returning "syntax error" > > My database is IDS 7.31 and my system is Win NT 2000 server. > > Any help would be great. > > Thanks in advance, > > Fred > > >

Many thanks, Will try this way Fred "Mike Aubury" <mike.aubury@aubit.com> a 'crit dans le message de news: 4208ef6a$0$4081$db0fefd9@news.zen.co.uk... > Aubit4GL has a windows build - it also works via ODBC and implements the > Unload command... > > If you have the ClientSDK - you could also use native connections (via > ESQL/C. > If you have esql/c another option is the aubit4gl 'asql' program - it's a > sort of dbaccess clone (still a work in progress -especially on windows, > but for your needs would probably be ideal..) > > If none of those appeal - I also have a simple C program to do the > specific > task of an ODBC unload - email me if you want details... > > > Bob wrote: > >> Hello, >> >> I need to make backup of my prime database while its running, prohibiting >> the use of dbexport. >> >> When using dbaccess i can do an unload to 'myfile' select * from mytable >> without any problem. >> >> As i have a large amount of tables i thought to make a little software to >> backup all the tables i needed to save using ODBC ... here is the >> problem, >> the same request which work on dbaccess does not work using sqlview >> connected by odbc. it keep on returning "syntax error" >> >> My database is IDS 7.31 and my system is Win NT 2000 server. >> >> Any help would be great. >> >> Thanks in advance, >> >> Fred >

Hi, I would gladly copie the data from one instance to the other if i knew how to do that :-) I have some basic knowledge of SQL, but i am not learned enough to do more than select, insert or update requests. I know right to nothing about triggers or stored procedure (i only know those features exists :-) or if i can launch a stored procedure by ODBC. If you can link me to some documentation, it would be great. Fred. "scottishpoet" <dryburghj@yahoo.com> a 'crit dans le message de news: 1107883031.072667.91860@o13g2000cwo.googlegroups.com... > why not write some SPL that copies the data directly from the one > isnatnce into the other? >

I will look at them Thanks, Fred "Art S. Kagel" <kagel@bloomberg.net> a 'crit dans le message de news: 4209434B.2080709@bloomberg.net... > Bob wrote: >> Hello, > > Get my dbexport/dbimport replacement utility, myexport. It does not lock > the tables/database. There are several extensions, improvements including > using onpload for exporting and exporting or importing in parallel to > speed things up and reduce intra-table data inconsistencies when exporting > a 'live' database. You need the following packages from the IIUG Software > Repository: > My myexport and utils2_ak packages and Jonathan Leffler's sqlcmd package. > > Art S. Kagel > >> I need to make backup of my prime database while its running, prohibiting >> the use of dbexport. >> >> When using dbaccess i can do an unload to 'myfile' select * from mytable >> without any problem. >> >> As i have a large amount of tables i thought to make a little software to >> backup all the tables i needed to save using ODBC ... here is the >> problem, the same request which work on dbaccess does not work using >> sqlview connected by odbc. it keep on returning "syntax error" >> >> My database is IDS 7.31 and my system is Win NT 2000 server. >> >> Any help would be great. >> >> Thanks in advance, >> >> Fred >> >>

Oops, look like its some linux tools ... at my great shame ... i am still using M.....T os :-) windooze Will try them someday if i can manage to convert my boss to linux :-) Fred "Art S. Kagel" <kagel@bloomberg.net> a 'crit dans le message de news: 4209434B.2080709@bloomberg.net... > Bob wrote: >> Hello, > > Get my dbexport/dbimport replacement utility, myexport. It does not lock > the tables/database. There are several extensions, improvements including > using onpload for exporting and exporting or importing in parallel to > speed things up and reduce intra-table data inconsistencies when exporting > a 'live' database. You need the following packages from the IIUG Software > Repository: > My myexport and utils2_ak packages and Jonathan Leffler's sqlcmd package. > > Art S. Kagel > >> I need to make backup of my prime database while its running, prohibiting >> the use of dbexport. >> >> When using dbaccess i can do an unload to 'myfile' select * from mytable >> without any problem. >> >> As i have a large amount of tables i thought to make a little software to >> backup all the tables i needed to save using ODBC ... here is the >> problem, the same request which work on dbaccess does not work using >> sqlview connected by odbc. it keep on returning "syntax error" >> >> My database is IDS 7.31 and my system is Win NT 2000 server. >> >> Any help would be great. >> >> Thanks in advance, >> >> Fred >> >>

Bob wrote: > Oops, look like its some linux tools ... at my great shame ... i am still > using M.....T os :-) windooze > > Will try them someday if i can manage to convert my boss to linux :-) > > Fred <SNIP> Well, they are a bit UNIX centric, however, they do not have to run on the server, if you have any UNIX/Linux boxes which have connectivity to the database, you can use the tools from that client. I've found it's actually more efficient sometimes to NOT run IO intensive utilities on the server anyway. Also, there is the other discussion of direct copying between servers, look at my dbcopy utility, which just need CSDK & a C compiler and can be compiled & run in Windoze. Dbcopy will copy data directly from table to table in a very fast and efficient manner even between different databases or servers. It's in the utils2_ak package. Art S. Kagel

Many thanks, Will try that tomorrow ... Fred. "Art S. Kagel" <kagel@bloomberg.net> a 'crit dans le message de news: 420A2D63.2050809@bloomberg.net... > Bob wrote: >> Oops, look like its some linux tools ... at my great shame ... i am still >> using M.....T os :-) windooze >> >> Will try them someday if i can manage to convert my boss to linux :-) >> >> Fred > <SNIP> > Well, they are a bit UNIX centric, however, they do not have to run on the > server, if you have any UNIX/Linux boxes which have connectivity to the > database, you can use the tools from that client. I've found it's > actually more efficient sometimes to NOT run IO intensive utilities on the > server anyway. > > Also, there is the other discussion of direct copying between servers, > look at my dbcopy utility, which just need CSDK & a C compiler and can be > compiled & run in Windoze. Dbcopy will copy data directly from table to > table in a very fast and efficient manner even between different databases > or servers. It's in the utils2_ak package. > > Art S. Kagel