[how to] Postgresql table with one integer column, sorted index, with duplicate primary key

---

• Postgresql table with one integer column, sorted index, with duplicate primary key
• SQL Reporting Services, filtering on Field not in DISTINCT SELECT statement?
• Insert missing rows to subscriber of transactaniol replication
• Open source tool for creation of tables from csv files [closed]
• How to properly troubleshoot Merge Replication False Conflicts
• Trigger for database
• Analyse MySQL General Query Log in Real-time?
• How does Sql server will identify Tomcat server? [closed]
• max_allowed_packet in mySQL
• Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?
• Seek predicate not using all available columns
• Can a database detect that data is out of sequence if each datapoint has a sequential ID
• Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?
• Update performance: clustered versus covering index
• Mysql settings for query_cache_min_res_unit
• Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating
• Delete SQL Server logins on replicated server
• Merging two Access tables into one
• DB2 db2fm proccess
• How to snapshot or version a relational database when data changes?
• Generic SQL Job Scheduler for multiple RDBMS's?
• Rent weekly cost database design
• Proper procedure for migrating a MySQL database to another Debian machine?
• How to add 'root' MySQL user back on MAMP?
• MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it?
• MySQL replication - some values are not being replicated
• Database Link from Oracle 10g on Windows over to MySQL database
• SQL Server 2012 Always On Availability Group Reporting Disconnected Replica
• Slow insert with MySQL full-text index

Postgresql table with one integer column, sorted index, with duplicate primary key Posted: 23 Jun 2013 07:47 PM PDT I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be deleted by a WHERE clause on the integer. The table is used like a set. Efficient ORDER BY+LIMIT on the column/rows, and DELETING with a WHERE-clause for that integer INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID. (That's why duplicates must be allowed, the check for primary keys would cause more disc seeks and can fail transactions and cause deadlocks) INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks. There shall not be additional btree files for this table, so no secondary indexes The rows should occupy not much space, e.g. have no OIDs, so that many fit in one page. I cannot think of a solution that solves all of this. The easiest thing would be a PRIMARY INDEX on that one column. But this won't allow duplicates. Also I could use a secondary index, but this would cause more operations per INSERT and more disc access. I am really bound by disc seeks here. Currently my best solution would compromise on the last bullet point: I could just a PRIMARY KEY covering the integer column and also a dummy column, like an OID, a timestamp column or a SERIAL column. Then every row (=primary key) would be unique. But this would increase disc space and reduce the amount of rows per page. Is there any chance of having a kind of primary key together with duplicates? Can I control what the main btree file on disc is organized by? (I posted a related question like this question on StackOverflow, but I was very unclear about my goals. I hope this doesn't count as spam, this question here is completely different)

SQL Reporting Services, filtering on Field not in DISTINCT SELECT statement? Posted: 23 Jun 2013 08:12 PM PDT I am using MS SQL Report Builder 3.0 to generate a report for SQL Reporting Services. I have a dataset that includes the columns AppointmentDate, PatientID, and InsuranceCarrier. I want to find out the number of DISTINCT patients that visited within a certain timeframe (begining AppointmentDate to ending AppointmentDate). The only time a patient should be present more than once in the query is if they had a different insurance carrier on a repeat visit. If I perform the following query: SELECT DISTINCT AppointmentDate, PaientID, InsuranceCarrier FROM Encounters WHERE AppointmentDate >= @beginningofdaterange AND AppointmentDate <= @endofdaterange Then I get a list of each patient and insurance carrier that visited in that date range. Unfortunately, since each AppointmentDate is also distinct, the Patients get duplicated for each individual appointment date. For example, if Patient X shows up twice in that timeframe specified, it will show both appointments. If I remove the AppointmentDate from my SELECT statement, then under Dataset properties in the Report Builder 3.0 I can no longer Filter based on the AppointmentDate expression. I could do my filtering directly in the WHERE clause of the T-SQL statement, but that means I cannot use the run-time Report Parameters input by the user. This is a problem because I am filtering based on Report Parameters that the user selects when running the report. They input the starting AppointmentDate and a "18-months prior" Parameter is calculated as the beginning and end AppointmentDates to filter against. So how do I include the AppointmentDate so that I can filter with it, but not include it in my DISTINCT SELECT so that it properly DISTINCTifies my data.

Insert missing rows to subscriber of transactaniol replication Posted: 23 Jun 2013 02:28 PM PDT I have established MSSQL replication between sql2005 and sql2008r2 server. SQL2005 Server is publisher while 2008 is subscriber. Replicated database is about 40GB. My replication was working most of time OK for few years. Few day ago I sow that I have missing rows inside few tables. When I check manualy I see whic rows i have missing. When I check replication monitor I see no errors and all looks nice. My trace tokens said that there is no lattency. But I know that there is no rows. Now I want to manualy transfer these rows from publisher to subscriber. I do not want to reinitialize my replication because when I do that. All my statistic go away and my users become to suffer whit performaces when start using fresh database. Other thing that my Publisher sql 2005 is very poor with performance and reincialization took a long time to finish and I do not have enough time to stop my app during that proccess. Is there an easy way to i force publisher to resend missing data to subscriber beside reincilizing hole replication again ? Is there a hay to I keep my statistic and indexes on subscriber database and after reintizailiating that, So I do not lose my performace.

Open source tool for creation of tables from csv files [closed] Posted: 23 Jun 2013 08:59 AM PDT Is anyone familiar with an open source tool or a ddl creation wizard that does the data profiling of the import .csv file and offers creation of the postgres compatible table from it with suggested types?

How to properly troubleshoot Merge Replication False Conflicts Posted: 23 Jun 2013 05:20 AM PDT We have SQL Server 2008 R2 Merge Replication setup. Lately I started getting conflicts for some tables, and when I check the conflict viewer, I can see that the winner & the loser column values are identical !! I am sure that these data are being entered bu only one subscriber. I used sp_showrowreplicainfo to get more details about the conflicting row & I got 2 rows : one for the Publisher & one for the subscriber with both rows having the "version" as 1 I also implemented Brandon's method: Auditing changes in Merge Replication, but it was showing only a normal insert !! Would you please tell me how to start troubleshooting this kind of conflict ?

Trigger for database Posted: 23 Jun 2013 08:15 AM PDT As far as I understand Trigger can be written for the table of the database. Now I want the trigger for database. The database can contain multiple table, If there is any change even in one table the trigger will fire says update table(another database table) set status='database changed'. Note: One trigger for whole database. I hope you understand my requirement, If you have any suggestion instead of trigger let me know.

Analyse MySQL General Query Log in Real-time? Posted: 23 Jun 2013 03:32 PM PDT We want to use mysql general query log to do real-time monitoring and auditing. Currently our approach is: set general_log=on; sleep 15m; set general_log=off; scp & rm xxx.log; set general_log=on;... But the main problem is when turn on/off general log it'll cause a peak of slow query. I also thought of another approach: turn on genlog; tail -f it and send out the log; periodically truncate the logfile (with "> xxx.log" or "cat /dev/null >xxx.log"). I'm wondering whether it's practical. If only mysql would provide some built-in general log message queue stuff...

How does Sql server will identify Tomcat server? [closed] Posted: 23 Jun 2013 01:31 AM PDT I have application in Tomcat server and I have sql server 2012 Database. This is the concept what I want to work: Initially the Database will send the message to application says that database getting changed. Then the application(java, webMathematica, etc.) from Tomcat server fire the query to fetch the data from database. Condition: In my scenario user doesn't have access to application(java, webMathematica, etc.) he has only access to Database. Initially I start the tomcat server and sql server. Now I want to know whether database(sql server) can start/initiate the application, so that the application(java, webMathematica, etc.) can fetch the data from data base for computation. Note: I have another database B to store the computed data of application.

max_allowed_packet in mySQL Posted: 23 Jun 2013 08:46 AM PDT I tried all three method explained here to max_allowed_packet. But no one changes its value in my MySQL 5.6. I use show variables like '%max_allowed_packet%' to see its current value. But it always is 12582912. Only changing its value in my.ini is effective. What is wrong?

Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived? Posted: 23 Jun 2013 04:53 PM PDT The pg_xlog directory for my master cluster looks like this: -rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001 -rw------- 1 postgres postgres 16M Jun 21 22:42 000000010000000000000002 -rw------- 1 postgres postgres 16M Jun 21 23:42 000000010000000000000003 -rw------- 1 postgres postgres 16M Jun 22 00:42 000000010000000000000004 -rw------- 1 postgres postgres 16M Jun 22 01:42 000000010000000000000005 -rw------- 1 postgres postgres 16M Jun 22 01:49 000000010000000000000006 -rw------- 1 postgres postgres 16M Jun 22 01:55 000000010000000000000007 -rw------- 1 postgres postgres 16M Jun 22 02:05 000000010000000000000008 -rw------- 1 postgres postgres 16M Jun 22 02:30 000000010000000000000009 -rw------- 1 postgres postgres 16M Jun 22 02:50 00000001000000000000000A The pg_xlog/archive_status directory looks like this: -rw------- 1 postgres postgres 0 Jun 22 01:49 000000010000000000000006.done -rw------- 1 postgres postgres 0 Jun 22 01:55 000000010000000000000007.done -rw------- 1 postgres postgres 0 Jun 22 02:05 000000010000000000000008.done -rw------- 1 postgres postgres 0 Jun 22 02:30 000000010000000000000009.done When I first turned on archiving 006 was the current segment, but why weren't 001-005 archived anyway? Last time I set up archiving on an existing cluster (which was yesterday - I've been experimenting), all log segments were archived when I turned archiving on, despite the pg_xlog directory containing more than 50 previous log segments. Relevant settings: wal_level = hot_standby archive_mode = on wal_keep_segments = 2048 # I know, right :) archive_timeout = 3600 archive_command = 'rsync -aq --delay-updates --ignore-existing %p postgres@db-slave:/mnt/pgsql/' Note: I did change all of these settings (including was_level, which was minimal previously) when I turned on archiving earlier. Could it be that my wal level wasn't sufficient to warrant archiving, or because there is a mismatch between the prior level and the current level?

Seek predicate not using all available columns Posted: 23 Jun 2013 07:34 PM PDT I have a strange query compilation issue which is hard to reproduce. It only happens under high load and cannot be easily repeated. There is a table T with columns A,B,C,D. There is a non-unique clustered index on T(A, B, C, D). There is a query SELECT * FROM T WHERE A=@P1 AND B=@P2 AND (C=@P3 OR C=@P4) AND D=@P5. The seek condition is on all columns of the clustered index, the 3-rd column has an OR. The problem is that the query plan for this query has Seek Predicate only on A and B! The predicate on C and D is an ordinary predicate, so this means that search tree on columns C and D is not utilized. The data types for all parameters match column data types. Could anyone provide any hints on why this could be happening? SQL version is 2008 R2 (SP1) - 10.50.2789.0 (X64)

Can a database detect that data is out of sequence if each datapoint has a sequential ID Posted: 23 Jun 2013 06:57 AM PDT Can a database detect that data is out of sequence if each datapoint has a sequential ID? E.G A trading DB server receives trade ID 9, and next receives trade ID 11. In this case, I would like the DB to return an error because trade ID 10 is missing. Is this possible? I don't need the code, just some context and the name of the solution. E.G. would this be some sort of DB input validation command, and how would it be enabled?

Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database? Posted: 23 Jun 2013 10:39 AM PDT A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I disagreed and provide my evidence below. If you disagree with me or have a better rebuttal, than by all means supply it. I am using AdventureWorks2012 on this version of SQL Server: SELECT @@VERSION; Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) Having loaded the database, I run the following query: Firstly, run Jonathan K's AW fattening script found here: AW Get Fat --------------------------- -- Step 1: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO SELECT OBJECT_NAME(p.object_id) AS [ObjectName] , p.object_id , p.index_id , COUNT(*) / 128 AS [buffer size(MB)] , COUNT(*) AS [buffer_count] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC; The result is shown here: Detach and re-attach the database and then re-run the query. --------------------------- -- Step 2: Detach/Attach --------------------------- -- Detach USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012' GO -- Attach USE [master]; GO CREATE DATABASE [AdventureWorks2012] ON ( FILENAME = N'C:\sql server\files\AdventureWorks2012_Data.mdf' ) , ( FILENAME = N'C:\sql server\files\AdventureWorks2012_Log.ldf' ) FOR ATTACH; GO What is in the bpool now? --------------------------- -- Step 3: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO SELECT OBJECT_NAME(p.object_id) AS [ObjectName] , p.object_id , p.index_id , COUNT(*) / 128 AS [buffer size(MB)] , COUNT(*) AS [buffer_count] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC; And the result: Are all the reads logical at this point? -------------------------------- -- Step 4: Logical Reads Only? -------------------------------- USE [AdventureWorks2012]; GO SET STATISTICS IO ON; SELECT * FROM DatabaseLog; GO SET STATISTICS IO OFF; /* (1597 row(s) affected) Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads 0, read-ahead reads 768, lob logical reads 94, lob physical reads 4, lob read-ahead reads 24. */ And we can see that the buffer pool was not totally blown away by the detach/attach. Seems like my buddy was wrong. Does anyone disagree or have a better argument? Another option is to offline and then online the database. Let us try that. -------------------------------- -- Step 5: Offline/Online? -------------------------------- ALTER DATABASE [AdventureWorks2012] SET OFFLINE; GO ALTER DATABASE [AdventureWorks2012] SET ONLINE; GO --------------------------- -- Step 6: Bpool Stuff? --------------------------- USE [AdventureWorks2012]; GO SELECT OBJECT_NAME(p.object_id) AS [ObjectName] , p.object_id , p.index_id , COUNT(*) / 128 AS [buffer size(MB)] , COUNT(*) AS [buffer_count] FROM sys.allocation_units AS a INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id WHERE b.database_id = DB_ID() AND p.object_id > 100 GROUP BY p.object_id , p.index_id ORDER BY buffer_count DESC; It appears that the offline/online operation worked a lot better.

Update performance: clustered versus covering index Posted: 23 Jun 2013 08:51 PM PDT I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table looks like this: Controller: Int ExecutionTime: DateTime Result: Int To maximize the efficiency of my queries, I need two indexes. (Result Includes Execution Time) and (Controller, ExecutionTime). These two indexes fully cover my queries - all information is served directly from the indices, no table lookups required. I chose nonclustered indices because I was worred about the performance hit using a clustered index with so many updates. But it occurs to me that since I am fully covering the queries, this might not be a valid concern - perhaps my covering, nonclustered indices require the same amount of maintenance as a clustered index would. So my question: In a table with a lot of inserts/updates, will a covering, nonclustered index usually have a lower UPDATE performance hit than a clustered index? Thanks for your time and help!

Mysql settings for query_cache_min_res_unit Posted: 23 Jun 2013 09:36 AM PDT What is the best setting for query_cache_min_res_unit for these results: +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 35327 | | Qcache_free_memory | 295242976 | | Qcache_hits | 236913188 | | Qcache_inserts | 49557287 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 7128902 | | Qcache_queries_in_cache | 195659 | | Qcache_total_blocks | 426870 | +-------------------------+-----------+ Do I need to change any other settings? My website creates very large results. This is the current setting: query_cache_min_res_unit = 4096 Info on the mysql dev website If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating Posted: 23 Jun 2013 05:36 PM PDT Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got strucks for five to fifteen minutes and suddenly process all the queued up list. I cannot understand what happens suddenly sometimes... The same queries for the same version, configuratios of the mysql in different server has no problem... Here is the innodb configurations: +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 1073741824 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 8589934592 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:4G;ibdata2:4G:autoextend | | innodb_data_home_dir | /var/lib/mysql | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 400 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | ON | | innodb_log_buffer_size | 33554432 | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_print_all_deadlocks | ON | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 10 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.5.30 | | innodb_write_io_threads | 10 | +---------------------------------+----------------------------------+ Can some one suggest me... why and what exactly happens

Delete SQL Server logins on replicated server Posted: 23 Jun 2013 11:36 AM PDT We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database. However, the SQL logins are not replicated from production to the remote server. What is the best way to replicate "logins" to the remote server with passwords and all? Here's my approach and I need to know if I'm going the right direction. Need help with a script that will drop all logins on the replicated server. EXEC [production server instance].[master].[dbo].[sp_help_revlogin] from the replicated server to get an accurate production list of logins. Find a way to actually execute the results from sp_help_revlogin that will script out the logins on the replicated server with hash passwords, SIDs etc. Any major problems with my approach? If not, I could use some help actually writing the scripts or outline the exact process for 1-3 (made some attempts but have had problems getting the scripts to work).

Merging two Access tables into one Posted: 23 Jun 2013 03:36 PM PDT I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?

DB2 db2fm proccess Posted: 23 Jun 2013 04:36 PM PDT Server is been up for 365 days, however i got some weird repeated procceses. Are these normal? ps -fea | grep db2fm db2inst1 643284 229516 29 May 25 - 212564:06 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S db2inst1 671770 229516 56 May 14 - 227447:02 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S db2inst1 757794 1237058 0 Apr 19 pts/7 0:00 /bin/sh /home/db2inst1/sqllib/bin/db2cc db2inst1 774232 229516 30 Sep 25 - 94218:54 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S db2inst1 962750 229516 30 Jul 18 - 145256:01 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S db2inst1 999450 229516 29 Aug 17 - 117710:27 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S db2inst1 1179898 229516 58 Nov 02 - 75788:49 /home/db2inst1/sqllib/bin/db2fm -i db2inst1 -m /home/db2inst1/sqllib/lib/libdb2gcf.a -S ps -fea | grep db2agent db2inst1 409770 680100 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 450750 778412 0 Apr 18 - 0:03 db2agent (idle) 0 db2inst1 618688 680100 0 Apr 19 - 0:00 db2agent (idle) 0 db2inst1 651440 680100 0 Nov 17 - 0:20 db2agent (DATABASEA) 0 db2inst1 655508 491676 0 Apr 19 - 0:04 db2agent (idle) 0 db2inst1 684038 680100 0 Mar 23 - 0:03 db2agent (DATABASEA) 0 db2inst1 790706 491676 0 Apr 19 - 0:00 db2agent (idle) 0 db2inst1 880672 680100 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 913438 778412 0 Nov 16 - 0:20 db2agent (idle) 0 db2inst1 946182 491676 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 991312 778412 0 Apr 17 - 0:16 db2agent (idle) 0 db2inst1 1077466 491676 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 1134726 680100 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 1142964 491676 0 Apr 19 - 0:00 db2agent (idle) 0 db2inst1 1233112 491676 0 Apr 19 - 0:00 db2agent (idle) 0 db2inst1 1261748 778412 0 Jun 15 - 0:18 db2agent (idle) 0 db2inst1 1384678 778412 0 Mar 23 - 0:27 db2agent (idle) 0 db2inst1 1404936 680100 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 1421368 778412 0 Mar 22 - 0:04 db2agent (idle) 0 db2inst1 1445936 491676 0 Apr 19 - 0:00 db2agent (DATABASEA) 0 db2inst1 1482864 491676 0 Jun 16 - 0:31 db2agent (idle) 0 db2inst1 1503440 778412 0 Jun 15 - 0:56 db2agent (idle) 0 db2inst1 1519842 778412 0 Mar 23 - 0:00 db2agent (DATABASEA) 0 db2inst1 1531946 680100 0 Apr 19 - 0:00 db2agent (idle) 0 db2inst1 1572884 680100 0 Apr 19 - 0:00 db2agent (idle) 0 Other info oslevel -g Fileset Actual Level Maintenance Level ----------------------------------------------------------------------------- bos.rte 5.3.0.40 5.3.0.0 db2fm -s -S Gcf module 'fault monitor' is NOT operable Gcf module '/home/db2inst1/sqllib/lib/libdb2gcf.a' state is AVAILABLE uptime 02:14PM up 365 days, 12:51, 6 users, load average: 6.69, 6.89, 6.97 db2level DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08020" with level identifier "03010106". Informational tokens are "DB2 v8.1.1.64", "s040812", "U498350", and FixPak "7"

How to snapshot or version a relational database when data changes? Posted: 23 Jun 2013 02:36 PM PDT My system receives data feeds. Each data feed will end up creating inserts and/or updates to most tables in the (relational) database. I need to capture the snapshot of what the entire database looked like after each data feed is received. Basically I need a way to version the database each time a data feed is run through the system. Note, by capturing a snapshot, I dont mean literally taking a snapshot of the database, but rather writing history records or some such mechanism so that I can query the database across "versions" to see what changed between versions (among other use cases) Do known data model designs exist that can capture a snapshot of a database version like this?

Generic SQL Job Scheduler for multiple RDBMS's? Posted: 23 Jun 2013 07:36 AM PDT I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex dataflows between the databases. Right now, the scheduling is done through independent systems, but I want to have one scheduler that controls the dataflows from beginning-to-end, and is able to script flows from MS SQL to RedShift, etc. Is there a system that can accomplish this already? I'm not a DBA, so I am guessing someone has had this problem before... Thanks in advance! EDIT: So one of our dataflows might look like this - file posted on SFTP --> run normal ETL routines --> compile final complete file --> send to customer/push to S3 --> Run SQL commands on Redshift to load* --> Nightly batch processing on RedShift* --> Unload to S3* --> Load into MySQL* *These are manually run using a tool that just connects via jdbc (can't remember the program) My DB-related experience is very light, so I was about to write some python scripts and schedule them in CRON, but that is custom and hard to expand - surely someone has had this problem before. We would like to be able to see a status of the job in one place, create new dataflows/ETL's between all three systems (like an SSIS job).

Rent weekly cost database design Posted: 23 Jun 2013 01:36 PM PDT I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a foreign key to the belonging BUILDING.ID. The BUILDING_UNIT table also refers to a table CATEGORY which tells whether the BUILDING_UNIT is of category A,B,C,D again with a foreign key pointing to CATEGORY.ID. Now the final cost of renting the building unit depends on its building, category and on the number of days it is rented and specific period of the year. We only rent them weekly so I might as well use weeks only however I'd like it to be as flexible as possible in the future. I cannot convince myself on a table which can represent this situation. Do I have to use a table with coefficients for each day of the year and then a table with coefficients for A,B,C,D and then a table with coefficients for each Building and then somehow calculate a result? Is there some standard and recognized implementation for problems of this type? Thank you EDIT: Notice the solution should abstract from the formula for calculating the cost which might change in the future. However I might be asked to make a specific week of the year, for building unit X inside building Y to cost 300$ while the week after 600$. Generally building units inside the same building and in the same week cost the same, however that might change in future so I'd like to treat already all specific cases.

Proper procedure for migrating a MySQL database to another Debian machine? Posted: 23 Jun 2013 10:36 AM PDT I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser -ppass --all-databases > dump.sql On the other server, I did a: mysql -uuser -ppass < dump.sql At first, everything seemed great. I could browse my databases in phpMyAdmin, but as soon as I tried logging in again, it failed. Turns out, my root password had been overwritten with the one from the older database. I wanted to reset it, but in order to do so, I would have needed to start mysqld_safe. Which I couldn't because the password for the debian-sys-maint user had been overwritten as well in the database. When I thought all hell had broken loose, I somehow reset both the root and debian-sys-maint passwords to the original values of the new server, and I managed to revert to a clean state. Since I obviously don't want to go down that road again, here's the question(s): Was I right with my approach of using a complete --all-databases dump? Was there something I needed to do in advance to reading in that dump to prevent this desaster from happening? Or even before creating the dump? If I'm going about this the wrong way: What is the proper procedure for migrating all databases and their users to another server? Note that I'm not that experienced with MySQL and server administration at all, so I might be missing something obvious. All the tutorials and how-tos I've found never mention anything like this and just talk about importing the complete dump.

How to add 'root' MySQL user back on MAMP? Posted: 23 Jun 2013 12:36 PM PDT On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?

MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it? Posted: 23 Jun 2013 03:31 PM PDT I need to change the wait_timeout for my MySQL database, but when I set this parameter in the my.cnf class (either in /etc or in /private/etc, because one of them is a link to the other), I still get the same default value when I type show variables in MySQL. How do I change that value on Mac OS X then? UPDATE I tried setting the value using mysql console, but it didn't help: mysql> set global wait_timeout = 333; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+ 10 rows in set (0.00 sec) It did work without the word "global", though, but that affects only the current connection.

MySQL replication - some values are not being replicated Posted: 23 Jun 2013 02:46 AM PDT MySQL Replication issues I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion. My structure looks like: 1) Master 2) slave (without SSL) 3) Slave (with SSL) what goes wrong is in some occasion, data are not replicated to slave. First my question, was if i someone is updating slaves. And i took off all privilege from all user other than read. which itself contradicts because both slave are not replicated and user's do not have permision on both slave. Ignored DB: binlog-ignore-db = mysql binlog-ignore-db = test replicate-ignore-db = mysql replicate-ignore-db = test Manual Test: I created new database, created table, new filed all was replicated to both slave. Updated works, altered works and delete works. But in some occasion when it misses out randomly i am unable to troubleshoot or narrow down problem area. Any suggestion? FYI, dont have skip-error in place.

Database Link from Oracle 10g on Windows over to MySQL database Posted: 23 Jun 2013 06:36 PM PDT Didn't see any answers on this subject so hoping for a miracle! :-) I've got an Oracle 10g database running on Windows Server 2003 and I'd like to create a database link to an external MySQL database which is running in the Amazon Web Service RDS cloud. We may have the option of upgrading the Oracle version to 11g if that would help? Has anyone done anything like this before? Thanks for your help! Josh

SQL Server 2012 Always On Availability Group Reporting Disconnected Replica Posted: 23 Jun 2013 04:56 AM PDT I'm trying to configure availability groups in a VM environment so I can run some tests. I think I've got the group created correctly, I can see the Always on group on both servers. However when I look at the dashboard for the group it has the following error "Availability replica disconnected This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED." I've checked the endpoints on both servers and they look correct. There are no firewalls running and both servers can see each other. What's the best way to debug this sort of error? Below is the TSQL I used to set all this up Primary Server CREATE ENDPOINT dbm_endpoint STATE=STARTED AS TCP (LISTENER_PORT=7022) FOR DATABASE_MIRRORING (ROLE=ALL) GO Secondary Server CREATE ENDPOINT dbm_endpoint STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=ALL) GO Primary Server CREATE AVAILABILITY GROUP AG1 FOR DATABASE TestDb REPLICA ON 'SQL1' WITH ( ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022', PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE), SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY), AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL ), 'SQL2' WITH ( ENDPOINT_URL = 'TCP://sql2.sql.sandbox.net:5022', PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE), SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY), AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL ); Secondary Server ALTER AVAILABILITY GROUP AG1 JOIN; Obviously I also restored the primary database to the secondary server as well. One thought, I didn't install the SQL Agent on either server, I'm guessing this is not needed for always on availability groups?

Slow insert with MySQL full-text index Posted: 23 Jun 2013 08:36 AM PDT I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? How can I get better performance from the INSERT? Is there perhaps an option to set when MySQL rebuilds the full-text index?

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610