[SQL Server 2008 issues] Correlated query

---

• Correlated query
• How to find when my SSRS service was last restarted ?
• mirroring
• how to update record in sql server
• Pushing around large volumes of data
• Join SQL Query help - Really stuck bad

Correlated query Posted: 23 Jun 2013 06:48 PM PDT hi this topic is related with correlated query my query is related with " Write a select command using a corelated subquery that selects the names and numbers of all customers with ratings equal to the maximum for their city."for this i triedSELECT abc.`CNAME`,abc.`RATING`FROM customers AS abcWHERE abc.`RATING`= (SELECT MAX(RATING) FROM customers WHERE ccity=abc.`CCITY` );which is working fine but later i triedSELECT cname,rating FROM customersWHERE rating= (SELECT MAX(RATING) FROM customers AS abc WHERE abc.`CCITY`=ccity );which gives me different resultmy question is what is difference between these two queries ? how they are executed exactly ? why they are showing different results

How to find when my SSRS service was last restarted ? Posted: 19 Jun 2013 06:38 PM PDT Expertrs,How to find when my SSRS service was last restarted ? Not the DB Engine.. Only Reporting services..Thanks in advance..Smith.

mirroring Posted: 23 Jun 2013 04:48 PM PDT Can we configure mirroring between servers in different domains ?

how to update record in sql server Posted: 23 Jun 2013 04:52 PM PDT hi friends i have small doubt in sql server plz tell me how to solve.table data contains like tablename: emp id , designation ,department 1 , abc(sales) , marketing 2 , ram(analysis) , finance 3 , banu(sales) , hrbased on that table whose designation 'sales' that must be change 'marketing'output like table: emp id , designation ,department 1 , abc(marketing) , marketing 2 , ram(analysis) , finance 3 , banu(marketing), hri try to useing update statement like update emp set designation= case designation when 'sales' then 'marketing' end but its not given exactely output.plese tell me query how to solve this issuse in sql server

Pushing around large volumes of data Posted: 23 Jun 2013 02:00 AM PDT I have a recurring theme problem I'm trying to help solve where I work. We take a lot of historical line item transactional data and move it through various phases within SQL: Raw (unedited), Staging (for customer review), Production (after approval of staging).While the data is large in volume, we're not talking about Google or Twitter volumes of data. I'm talking about millions of rows of charge detail history for medical software.What I'm trying to best understand is the way to get things from point A to B with the minimal time and effort involved. Part of the issue is transactionally (volume of executions simultaneously running on the same server) and part of it is syntax (am I writing these movements most efficiently).What I'd love is some overall help to understand where I might be gaining more efficiencies in some of the basics. An example I'm working through this weekend is the following:Part of this detail history involves first cross checking field lengths, etc, to ensure accuracy before moving information into staging. One of the final steps for me is to concatenate three fields into the final patient account number on all the detail line items in history. When I first wrote the statement, it literally looked like the following:update temp_raw_chargedetailset Encounternumber = Field1+Field2+Field3One of my coworkers (former DBA/SQL guru) rewrote it quickly for me to the following recently:Set RowCount 100000update temp_raw_chargedetailset Encounternumber = Field1+Field2+Field3Where Encounternumber is NullWhile @@ROWCOUNT>0Begin update temp_raw_chargedetail set Encounternumber = Field1+Field2+Field3 Where Encounternumber is NullEndSet RowCount 0In initial tests, it is running quicker overall, but just this latter statement alone has been running on a 27 million record set of rows since 2 pm on Friday afternoon and here it is 10 am on Sunday. There is nothing else running on this server right now other than select statements I've been running to see where other databases/accounts stand. I just have a feeling something else could be done to mitigate some of this timing as it feels like I'm waiting a ridiculous amount of time for something that would seem somewhat superficial to complete processing.Any initial thoughts? I'm happy to provide additional details to start narrowing down how I could be thinking things through more efficiently. Thanks!

Join SQL Query help - Really stuck bad Posted: 22 Jun 2013 07:15 PM PDT I am in need of functionality for getting the Country name from the IP.I have to show the country in front of every unique visitor I have on my site. (Previously, I asked a question regarding a similar approach, but I changed it now.)The output I expect would be something as follows:-[quote] Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....[/quote]The output is showing that 482 Unique visitors have come from US, 213 from Korea, and so on.To achieve this, I divided this task into two part, first getting all the IPs of the Unique user and the second part is to search two tables with country/IP information to get the country.We then aggregate data based on the top output table shown. I am going to show the queries I wrote for each part. Below, I have shown the Structure and Sample Data of the tables used.First Part - Getting Unique Visitors on my site------------------------------------------------[quote] SELECT a.uid, a.hostname, COUNT( * ) AS times FROM login_activity a WHERE a.uid =83 GROUP BY a.hostname ORDER BY times[/quote]This shows the output as below. It means that the USer with ID 83 has 80 unique visits to the site.:-[quote] uid | hostname | times ------------------------------------- 83 | 157.191.122.36 | 80[/quote]This is wrong as I should be getting the no. of hits for each IP. But somehow I am unable to do so.Second Part - Finding the country based on IP---------------------------------------------[quote] SELECT cc, cn FROM ip NATURAL JOIN cc WHERE INET_ATON( "157.191.122.36" ) BETWEEN START AND END[/quote] This gives me the output as:- [quote] cc | cn -------- US | United States[/quote]This is correct, as it's giving me the correct Country based on the IP provided.With the help of these three tables and two queries I've shown on top, I need to show the following result:-[quote] Country Visited | No. of Times _______________________________ United States | 482 Korea | 213 Pakistan | 123 ....[/quote]I will be very thankful if you can help me in any way ...Appendix (Structure and Data of the Tables)-------------------------------------------**login_activity**---(Structure)[quote] CREATE TABLE IF NOT EXISTS `mslop_login_activity` ( `aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).', `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.', `host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.', `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.', `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.', PRIMARY KEY (`aid`), KEY `aid` (`aid`), KEY `uid` (`uid`), KEY `timestamp` (`timestamp`) );[/quote](Data)[quote] INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES (1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356), (873, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1369773601), (883, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369774959), (893, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31', '157.191.122.36', 1369818602), (903, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369838690), (913, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0; AskTbORJ/5.15.23.36191)', '157.191.122.36', 1369840224), (923, 83, 'Mozilla/5.0 (Windows NT 6.1; rv:21.0) Gecko/20100101 Firefox/21.0', '157.191.122.36', 1369841748), (933, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370255417), (943, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258059), (953, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258060), (963, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299827), (973, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299852), (983, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299899), (993, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299920), (1003, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299995), (1013, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319811), (1023, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319835), (1033, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319939), (1043, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320312), (1053, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320640), (1063, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320713), (1073, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363702), (1083, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363987), (1093, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364021), (1103, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364028), (1113, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370365896), (1123, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370366087), (1133, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370367070), (1143, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368567), (1153, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368575), (1163, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370379345), (1173, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370427302), (1183, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370503422), (1193, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370534329), (1203, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370866207), (1213, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370871353), (1223, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; chromeframe/26.0.1410.43; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370877702), (1233, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371015245), (1243, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1371069245), (1253, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371117509), (1263, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371137716), (1273, 83, 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)', '157.191.122.36', 1371197327), (1283, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371427531), (1293, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371552899), (1303, 83, 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371635304), (1313, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371770752), (1323, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371771394), (1333, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371771647), (1343, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772611), (1353, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772626), (1363, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371772931), (1373, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SIMBAR={37EF0ACB-CCB3-11E2-9611-E006E6BAE768}; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1371782438), (1383, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371782562), (1393, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.116 Safari/537.36', '157.191.122.36', 1371790767), (1403, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371834780);[/quote]**cc**-------(Structure)[quote] CREATE TABLE cc ( ci TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cc CHAR(2) NOT NULL, cn VARCHAR(50) NOT NULL );[/quote](Data)Since it's large, you can download it through this link :- [quote]http://d.pr/f/N3KT[/quote]**ip**------(Structure)[quote] CREATE TABLE ip ( start INT UNSIGNED NOT NULL, end INT UNSIGNED NOT NULL, ci TINYINT UNSIGNED NOT NULL );[/quote](Data)Since it's large, you can download it through this link:-[quote]http://d.pr/f/wcEy.[/quote]I apologize for this long post and will be very thankful for your help.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] SQL Agent tokens problem

---

SQL Agent tokens problem Posted: 23 Jun 2013 11:55 AM PDT I am trying to use SQL Agent tokens to specify the output file name within a SQL Agent job, for example:F:\SQLLogs\Daily_Batch_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).logHowever the STRTTM and TIME tokens are not producing HHMMSS output as expected, the hour component has leading zeros omitted.Does anyone know of a way to configure this?

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[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

[MS SQL Server] Strange issue - process in rollback not displaying rollback

---

Strange issue - process in rollback not displaying rollback Posted: 23 Jun 2013 05:04 AM PDT I have a killed process that isn't showing a rollback. when I execute kill [spid] i get a timer and a % for rollback. What it does show is that it's "awaiting command" and FCB_Replica_Write.I do have two pull replications on this server that i would gladly remove if it would speed this process up. one issue is that I do not have this replication setup in CM and the rollback is blocking the scripting processes to save the setup. Is anyone familiar with this? This is the first time I've run across this error. I do suspect a disk issue, but I cannot run a checkdb due to the rollback in progress.Any help is appreciated. Currently the idea is to just wait it out and let the rollback complete (instead of restarting and having it complete during restore)

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610