Test Db as subset of big production DB
Hello, I need to create a test DB from a large production DB (over 2TB). I am looking for a way to get a random spread of records from the tables of production DB to use in test (less than 1TB). I am considering BCP, some sort of select into (two separate servers with access to/from). Trying to find the method with the least impact on production. Any ideas would be appreciated. Thanks, rick_806
|
0 |
|
rick_806
8/19/2008 3:01:54 PM
sybase.ase.administration
7058 articles. 
2 followers.
3 Replies
514 Views
On Aug 19, 8:01=A0pm, rick_806 wrote: > Hello, > > I need to create a test DB from a large production DB (over > 2TB). I am looking for a way to get a random spread of > records from the tables of production DB to use in test > (less than 1TB). I am considering BCP, some sort of select > into (two separate servers with access to/from). Trying to > find the method with the least impact on production. Any > ideas would be appreciated. > You can try creating views with "where" conditions on primary key columns so that only subset of records are selected and bcp out those recods from view and bcp in into test server. -HTH Manish Negandhi [TeamSybase]
|
|
0 |
|
Manish
8/19/2008 3:22:24 PM
rick_806 wrote: > Hello, > > I need to create a test DB from a large production DB (over > 2TB). I am looking for a way to get a random spread of > records from the tables of production DB to use in test > (less than 1TB). I am considering BCP, some sort of select > into (two separate servers with access to/from). Trying to > find the method with the least impact on production. Any > ideas would be appreciated. > > Thanks, > rick_806 One approach to get a reasonable distribution of data might be to use optdiag to extract the statistics for the table, then parse out from that file the primary key values for each step. From those values, generate a series of select statements select top <n> * from table where <primarykey> > <value> <n> would be sized by how much data you wanted. You could create a proxy table on the test box that maps to the real table and do the series of "insert <localtesttable> select top <n> * from <proxytable> where <primarykey> > <value>" as an easy way to move the data across. Another approach would be to come up with some kind of hashing/filter function, build a view based on that, and bcp out of the view. For instance, to bcp out about 1/3rd of the values from prod table, assuming a numeric primary key and even distribution of primary key values create view bcpoutview as select * from productiontable where primarykey % 3 = 1 go
|
|
0 |
|
Bret
8/19/2008 3:29:52 PM
Hi, Thanks for the ideas. I will try it with a view. > On Aug 19, 8:01=a0pm, rick_806 wrote: > > Hello, > > > > I need to create a test DB from a large production DB > > (over 2TB). I am looking for a way to get a random > > spread of records from the tables of production DB to > > use in test (less than 1TB). I am considering BCP, some > > sort of select into (two separate servers with access > > to/from). Trying to find the method with the least > > impact on production. Any ideas would be appreciated. > > > You can try creating views with "where" conditions on > primary key columns so that only subset of records are > selected and bcp out those recods from view and bcp in > into test server. > > -HTH > Manish Negandhi > [TeamSybase]
|
|
0 |
|
rick_806
8/20/2008 11:04:29 PM
Similar Artilces:
[PATCH lib/DB.pm MANIFEST lib/DB.t] Add Tests for DB.pm
Here's a bunch of tests for DB.pm. Some bits aren't easily testable, and there's room for someone more knowledgeable or clever to come along to improve them even more. In the process of writing these tests, I patched one little thing in DB.pm, per the comment. With the patch, all pass. -- c --- lib/~DB.pm Fri Nov 23 14:04:53 2001 +++ lib/DB.pm Fri Nov 23 14:48:12 2001 @@ -406,8 +406,7 @@ $name = "main" . $name if substr($name,0,2) eq "::"; my($fname, $from, $to) = ($DB::sub{$name} =~ /^(.*):(\d+)-(\d+)$/); if ($from) { - # XXX t...
[REPATCH lib/DB.pm MANIFEST lib/DB.t] Rework DB.pm tests
Based on Schwern's suggestions that my ideal Test::More::is() does not match reality, here's a version of the DB.pm tests that explicitly tests definedness. I also fixed a couple of bugs in the test. It passes, with the attached DB.pm patch applied. -- c --- lib/~DB.pm Fri Nov 23 14:04:53 2001 +++ lib/DB.pm Fri Nov 23 14:48:12 2001 @@ -406,8 +406,7 @@ $name = "main" . $name if substr($name,0,2) eq "::"; my($fname, $from, $to) = ($DB::sub{$name} =~ /^(.*):(\d+)-(\d+)$/); if ($from) { - # XXX this needs local()-ization of some sort - *D...
Production and test db
Hi all!My web application need to have 2 environments (production and test) and I am using Access 2003. If I make a copy of my production db and make it test, how do I make it swtichable in ASP.net btw two environments? And how do I handle my Crystal Reports that always link to the production db (using DSN)?Thanks in advance! Sam ...
DB DB LG ML PR DB
http://www.youtube.com/watch?v=h2-zD5lj8Hg Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg WTF!!! -- Strong Bo "Reality is merely an illusion, albeit a very persistent one." -- Albert Einstein (1879-1955) Play it again... then you'll understand. Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg That's one of the more brain-damaged Youtube clips I've seen, yet kinda catchy! -- Bruce ...
membership DB, profile DB, and role DB
I use the following code to programatically cycle through each record in an mdb database and create users with the Membership.CreateUser method. The code also creates a profile for the same user before moving to the next record. The membership and profile DB is on an SQL 2000 server. How can I assign all these users to the role of user in this section of code? Also any idea why only 104 out of 148 records are added to the Membership table while all 148 profiles are created? A sample record would be:UserName DIST ...
COPYING TABLES FROM TEST DB TO LIVE DB
Hi ya all, I had a really quick question. I have created a new table using Sybase Central and have populated it using INSERT statements via ISQL. Everything works beautifully! I originally created this table in our TEST DB. Is there an easy way for me to export or transport it to our LIVE DB without rebuilding the table in LIVE and repopulating the table in LIVE via the INSERT statements? I guess what I'm trying to ask is that once a table has been created in a TEST DB is there some fancy export command which will copy its data type, columns, and values to the LIVE DB? Could you ge...
How to Update a DB Test to Real DB in term of Design
Hi all, I got a problem, i'd like to update my DB in production in terme of design and data from the DB test. In other words, I added many tables to my design. Therefore, i'd have to update the real DB on the server. Is there a way to do so with MSSQL? without doing it manually. Thanks, The easiest way would be to do a backup on the dev server and a restore on the production server. Failing that, Red Gate (www.redgate.com) has a number of tools that make synchronizing data and code lots easier.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My...
Prod DB fine but want to connect to a test DB.
PB Newbie so please forgive.... PB 8.0.1 (build 8004). My application works fine but want to connect to a different SQL Server 7.0 database for testing. Tools/Database Profile...brings up a list of database selections. By selecting the DB and clicking on connect a checkmark appears upon the selected DB(test). The problem is that although it says I'm connect the application doesn't appear to be retrieving data from the test DB. Any ideas? Thank you!!! You are probably connecting to the database in your application. The database you connect to in the code overr...
How to move data from AIX db to Linux db, same DB Version
Is there an easy way to do this for an entire database? BCP is giving me problems and the database has 234 tables, 281 Stored procedures, etc etc. I need data, indexes, sp, etc. The database dump command seems to dump to binary compatibility only. Any help is greatly appreciated. This problem has been bugging me for about 3 weeks now and its about to become one of those urgent, priority one problems which management just loves. TIA, Bill BMC Sqlbacktrack has a logical dump feature which does a good job, but the sqlbacktrack license may not be cheap. Using DBSchema and b...
Deployment mechanism for CONTENT from test db to production.
Hi Everyone, I've got a project I'm about to embark on that I'm sure someone in the big wide world of DotNetNuke must have considered or even done before. The company I work with has two installations of DNN running. There's one on a test server and then there's the live one on our production server. Now, I'm totally fine with deploying code changes. I've done that a number of times. The thing that is tricky is deploying content changes. I should mention at this point that the website runs on DNN 1.0.10. We're possibly going to update that soon as well, although it'll...
web db and main db
I have a website db and the main company db. Both are ASA8. My query is how do i maintain my inventory records in my main db when I sell items on the web? can stored procedures be used across 2 different connections? Would appreciate some guidance how to achieve this. Thanks See the ASA SQL User's Guide, 15. Accessing Remote Data. You will create a remote server, and proxy tables and/or remote procedures, on the web db so it can see and write to the main db. Richard "MJN" <chuck1rar@mbi.nifty.com> wrote in message news:401687aa$1@forums-1-dub... > ...
db 5 and db 6
if the db create by sql anywhere 5 can be opened by adapter anywhere 6 Yes. ASA7 can even read SQLAnwhere 5 files. To use some of the new features of 6 or 7 you would have to upgrade the 5 database. -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase Search Deja: http://www.pfcguide.com/_newsgroups/search.asp "lhl" <liuhualiang@is.dlrin.edu.cn> wrote in message news:uHBaP#KfBHA.235@forums.sybase.com... > if the db create by sql anywhere 5 can be opened by adapter anywhere 6 > > ...
Local DB into ASA DB
HI Gurus, Is there any way I can convert my local ASA DB to network database, for example can I convert EASDEMODB.db into server database. I am using a local database for my application and I want to convert that DB to server DB. Thanks in Advance Vino As posted in ASE newsgroup Vino What yo want to do in not dependent on the database file, but on the engine you use. Any database can be started up in two ways: For only local connection, use dbeng?.exe (? signifies ASA version) in your start line. For network connections, use dbsrv?.exe. Bart wrote: > H...
DB Administration
When I run an update query under script control, afterwards, I can check things like SQLCA.Nrows to see how many rows have been or will be affected by the update query. Is there something similar that can be used when doing queries using DB administration? Thank You Woody Splawn Woody, it depends on the database you are using. In SQL Anywhere (and, probably, SYBASE) it is select @@rowcount; I'm not sure about how can you count exact number of rows which WILL be affected by the query. You can get plan estimate if in Database Administration you go to Design/Explain SQL......
Here's a bunch of tests for DB.pm. Some bits aren't easily testable, and there's room for someone more knowledgeable or clever to come along to improve them even more. In the process of writing these tests, I patched one little thing in DB.pm, per the comment. With the patch, all pass. -- c --- lib/~DB.pm Fri Nov 23 14:04:53 2001 +++ lib/DB.pm Fri Nov 23 14:48:12 2001 @@ -406,8 +406,7 @@ $name = "main" . $name if substr($name,0,2) eq "::"; my($fname, $from, $to) = ($DB::sub{$name} =~ /^(.*):(\d+)-(\d+)$/); if ($from) { - # XXX t...
[REPATCH lib/DB.pm MANIFEST lib/DB.t] Rework DB.pm tests
Based on Schwern's suggestions that my ideal Test::More::is() does not match reality, here's a version of the DB.pm tests that explicitly tests definedness. I also fixed a couple of bugs in the test. It passes, with the attached DB.pm patch applied. -- c --- lib/~DB.pm Fri Nov 23 14:04:53 2001 +++ lib/DB.pm Fri Nov 23 14:48:12 2001 @@ -406,8 +406,7 @@ $name = "main" . $name if substr($name,0,2) eq "::"; my($fname, $from, $to) = ($DB::sub{$name} =~ /^(.*):(\d+)-(\d+)$/); if ($from) { - # XXX this needs local()-ization of some sort - *D...
Production and test db
Hi all!My web application need to have 2 environments (production and test) and I am using Access 2003. If I make a copy of my production db and make it test, how do I make it swtichable in ASP.net btw two environments? And how do I handle my Crystal Reports that always link to the production db (using DSN)?Thanks in advance! Sam ...
DB DB LG ML PR DB
http://www.youtube.com/watch?v=h2-zD5lj8Hg Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg WTF!!! -- Strong Bo "Reality is merely an illusion, albeit a very persistent one." -- Albert Einstein (1879-1955) Play it again... then you'll understand. Craig Leidy wrote: > http://www.youtube.com/watch?v=h2-zD5lj8Hg That's one of the more brain-damaged Youtube clips I've seen, yet kinda catchy! -- Bruce ...
membership DB, profile DB, and role DB
I use the following code to programatically cycle through each record in an mdb database and create users with the Membership.CreateUser method. The code also creates a profile for the same user before moving to the next record. The membership and profile DB is on an SQL 2000 server. How can I assign all these users to the role of user in this section of code? Also any idea why only 104 out of 148 records are added to the Membership table while all 148 profiles are created? A sample record would be:UserName DIST ...
COPYING TABLES FROM TEST DB TO LIVE DB
Hi ya all, I had a really quick question. I have created a new table using Sybase Central and have populated it using INSERT statements via ISQL. Everything works beautifully! I originally created this table in our TEST DB. Is there an easy way for me to export or transport it to our LIVE DB without rebuilding the table in LIVE and repopulating the table in LIVE via the INSERT statements? I guess what I'm trying to ask is that once a table has been created in a TEST DB is there some fancy export command which will copy its data type, columns, and values to the LIVE DB? Could you ge...
How to Update a DB Test to Real DB in term of Design
Hi all, I got a problem, i'd like to update my DB in production in terme of design and data from the DB test. In other words, I added many tables to my design. Therefore, i'd have to update the real DB on the server. Is there a way to do so with MSSQL? without doing it manually. Thanks, The easiest way would be to do a backup on the dev server and a restore on the production server. Failing that, Red Gate (www.redgate.com) has a number of tools that make synchronizing data and code lots easier.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My...
Prod DB fine but want to connect to a test DB.
PB Newbie so please forgive.... PB 8.0.1 (build 8004). My application works fine but want to connect to a different SQL Server 7.0 database for testing. Tools/Database Profile...brings up a list of database selections. By selecting the DB and clicking on connect a checkmark appears upon the selected DB(test). The problem is that although it says I'm connect the application doesn't appear to be retrieving data from the test DB. Any ideas? Thank you!!! You are probably connecting to the database in your application. The database you connect to in the code overr...
How to move data from AIX db to Linux db, same DB Version
Is there an easy way to do this for an entire database? BCP is giving me problems and the database has 234 tables, 281 Stored procedures, etc etc. I need data, indexes, sp, etc. The database dump command seems to dump to binary compatibility only. Any help is greatly appreciated. This problem has been bugging me for about 3 weeks now and its about to become one of those urgent, priority one problems which management just loves. TIA, Bill BMC Sqlbacktrack has a logical dump feature which does a good job, but the sqlbacktrack license may not be cheap. Using DBSchema and b...
Deployment mechanism for CONTENT from test db to production.
Hi Everyone, I've got a project I'm about to embark on that I'm sure someone in the big wide world of DotNetNuke must have considered or even done before. The company I work with has two installations of DNN running. There's one on a test server and then there's the live one on our production server. Now, I'm totally fine with deploying code changes. I've done that a number of times. The thing that is tricky is deploying content changes. I should mention at this point that the website runs on DNN 1.0.10. We're possibly going to update that soon as well, although it'll...
web db and main db
I have a website db and the main company db. Both are ASA8. My query is how do i maintain my inventory records in my main db when I sell items on the web? can stored procedures be used across 2 different connections? Would appreciate some guidance how to achieve this. Thanks See the ASA SQL User's Guide, 15. Accessing Remote Data. You will create a remote server, and proxy tables and/or remote procedures, on the web db so it can see and write to the main db. Richard "MJN" <chuck1rar@mbi.nifty.com> wrote in message news:401687aa$1@forums-1-dub... > ...
db 5 and db 6
if the db create by sql anywhere 5 can be opened by adapter anywhere 6 Yes. ASA7 can even read SQLAnwhere 5 files. To use some of the new features of 6 or 7 you would have to upgrade the 5 database. -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase Search Deja: http://www.pfcguide.com/_newsgroups/search.asp "lhl" <liuhualiang@is.dlrin.edu.cn> wrote in message news:uHBaP#KfBHA.235@forums.sybase.com... > if the db create by sql anywhere 5 can be opened by adapter anywhere 6 > > ...
Local DB into ASA DB
HI Gurus, Is there any way I can convert my local ASA DB to network database, for example can I convert EASDEMODB.db into server database. I am using a local database for my application and I want to convert that DB to server DB. Thanks in Advance Vino As posted in ASE newsgroup Vino What yo want to do in not dependent on the database file, but on the engine you use. Any database can be started up in two ways: For only local connection, use dbeng?.exe (? signifies ASA version) in your start line. For network connections, use dbsrv?.exe. Bart wrote: > H...
DB Administration
When I run an update query under script control, afterwards, I can check things like SQLCA.Nrows to see how many rows have been or will be affected by the update query. Is there something similar that can be used when doing queries using DB administration? Thank You Woody Splawn Woody, it depends on the database you are using. In SQL Anywhere (and, probably, SYBASE) it is select @@rowcount; I'm not sure about how can you count exact number of rows which WILL be affected by the query. You can get plan estimate if in Database Administration you go to Design/Explain SQL......
Web resources about - Test Db as subset of big production DB - sybase.ase.administration
Post-production - Wikipedia, the free encyclopediaPost-production is part of filmmaking and the video production process. It occurs in the making of motion pictures , television programs , radio ...
Grass fire at Strathnairn arts production facility near HoltFire burned through about 35 hectares of grassland in Canberra's north.
Grass fire at Strathnairn arts production facility near HoltFire burned through about 35 hectares of grassland in Canberra's north.
Study: Agency and Production Costs Keep Rising Despite Efforts to Cut ThemAgency, production and research costs make up around 40% of advertising budgets and around 20% of overall marketing budgets, and they're growing ...
Tesla Model X production line looks like a car factoryFiled under: Green , Plants/Manufacturing , Videos , Tesla , Crossover , Sedan , Electric , Luxury , Performance The Car Guide gets a video of ...
Industrial Production Declines Most in 3.5 Years, Down Eighth Time in Ten MonthsIndustrial production shocked to the downside this morning with a drop of 0.6%, the most in 3.5 years vs. an Econoday Consensus guess of -0.2%. ...
Industrial Production and Capacity Utilization - Business Insider DeutschlandEconomists had estimated that unseasonably warm weather contributed to a decline in industrial production in November
BitFury Announces Mass Production of Fastest and Most Effective 16nm ASIC Chip in the World
BitFury Group announced mass-production of its full-custom design 16nm Application Specific Integrated Circuit (ASIC) Chip, which will bring ...
BitFury Group announced mass-production of its full-custom design 16nm Application Specific Integrated Circuit (ASIC) Chip, which will bring ...
Fed: Industrial Production decreased 0.6% in NovemberFrom the Fed: Industrial production and Capacity Utilization Industrial production declined 0.6 percent in November after decreasing 0.4 percent ...
BREAKING NEWS x 2: Tesla Model X Production Kicks Into High Gear... up Model Xs in delivery vehicles and preparing to get them out to the eager folks on the [&hellip BREAKING NEWS x 2: Tesla Model X Production ...
Resources last updated: 12/21/2015 1:03:34 PM