How to make DISTINCT ON faster in PostgreSQL? I have a table station_logs in a PostgreSQL 9.6 database:

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone |
 level_sensor  | double precision            |
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

I'm trying to get the last level_sensor value based on submitted_at, for each station_id. There are around 400 unique station_id values, and around 20k rows per day per station_id.

Before creating index:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

 Unique  (cost=4347852.14..4450301.72 rows=89 width=20) (actual time=22202.080..27619.167 rows=98 loops=1)
   ->  Sort  (cost=4347852.14..4399076.93 rows=20489916 width=20) (actual time=22202.077..26540.827 rows=20489812 loops=1)
         Sort Key: station_id, submitted_at DESC
         Sort Method: external merge  Disk: 681040kB
         ->  Seq Scan on station_logs  (cost=0.00..598895.16 rows=20489916 width=20) (actual time=0.023..3443.587 rows=20489812 loops=$
 Planning time: 0.072 ms
 Execution time: 27690.644 ms

Creating index:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

After creating index, for the same query:

 Unique  (cost=0.56..2156367.51 rows=89 width=20) (actual time=0.184..16263.413 rows=98 loops=1)
   ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..2105142.98 rows=20489812 width=20) (actual time=0.181..1$
 Planning time: 0.206 ms
 Execution time: 16263.490 ms

Is there a way to make this query faster? Like 1 sec for example, 16 sec is still too much.

For only 400 stations, this query will be massively faster:

SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;

dbfiddle here
(comparing plans for this query, Abelisto's alternative and your original)

Resulting EXPLAIN ANALYZE as provided by the OP:

 Nested Loop  (cost=0.56..356.65 rows=102 width=20) (actual time=0.034..0.979 rows=98 loops=1)
   ->  Seq Scan on stations s  (cost=0.00..3.02 rows=102 width=4) (actual time=0.009..0.016 rows=102 loops=1)
   ->  Limit  (cost=0.56..3.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=102)
         ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..664062.38 rows=230223 width=16) (actual time=0.009$
               Index Cond: (station_id = s.id)
 Planning time: 0.542 ms
 Execution time: 1.013 ms  -- !!

The only index you need is the one you created: station_id__submitted_at. The UNIQUE constraint uniq_sid_sat also does the job, basically. Maintaining both seems like a waste of disk space and write performance.

I added NULLS LAST to ORDER BY in the query because submitted_at isn't defined NOT NULL. Ideally, if applicable!, add a NOT NULL constraint to the column submitted_at, drop the additional index and remove NULLS LAST from the query.

If submitted_at can be NULL, create this UNIQUE index to replace both your current index and unique constraint:

CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

Consider:

Does creating a unique constraint on a Postgres column remove the need to index it?
How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

This is assuming a separate table station with one row per relevant station_id (typically the PK) - which you should have either way. If you don't have it, create it. Again, very fast with this rCTE technique:

CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;

I use that in the fiddle as well. You could use a similar query to solve your task directly, without station table - if you can't be convinced to create it.

Detailed instructions, explanation and alternatives:

Optimize GROUP BY query to retrieve latest record per user
Select first row in each GROUP BY group?

Optimize index

Your query should be very fast now. Only if you still need to optimize read performance ...

It might make sense to add level_sensor as last column to the index to allow index-only scans, like joanolo commented.
Con: It makes the index bigger - which adds a little cost to all queries using it.
Pro: If you actually get index only scans out of it, the query at hand does not have to visit heap pages at all, which makes it about twice as fast. But that may be an insubstantial gain for the very fast query now.

However, I don't expect that to work for your case. You mentioned:

  ... around 20k rows per day per station_id.

Typically, that would indicate unceasing write load (1 per station_id every 5 seconds). And you are interested in the latest row. Index-only scans only work for heap pages that are visible to all transactions (bit in the visibility map is set). You would have to run extremely aggressive VACUUM settings for the table to keep up with the write load, and it would still not work most of the time. If my assumptions are correct, index-only scans are out, don't add level_sensor to the index.

OTOH, if my assumptions hold, and your table is growing very big, a BRIN index might help. Related:

Speed up creation of Postgres partial index

Or, even more specialized and more efficient: A partial index for only the latest additions to cut off the bulk of irrelevant rows:

CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

Chose a timestamp for which you know that younger rows must exist.
You have to add a matching WHERE condition to all queries, like:

...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

You have to adapt index and query from time to time.
Related answers with more details:

Index optimization with dates
Add datetime constraint to a PostgreSQL multi-column partial index
Indexed ORDER BY with LIMIT 1


Here is a version for SQL Server 2012 using the new LAG feature:

WITH CTE AS
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        LAG(Flag)
            OVER (ORDER BY EventDateTime) AS PrevFlag
    FROM MyTable
)
SELECT EventDateTime, Host, Flag
FROM CTE
WHERE Flag <> PrevFlag;

Here is a version for SQL Server 2008 using a self join:

WITH CTE AS
(
    SELECT
        EventDateTime,
        Host,
        Flag,
        ROW_NUMBER()
            OVER (ORDER BY EventDateTime) AS RowNum
    FROM MyTable
)
SELECT
    C.EventDateTime,
    C.Host,
    C.Flag
FROM CTE AS C
    INNER JOIN CTE AS D
    ON C.RowNum = D.RowNum +1
WHERE C.Flag <> D.Flag;


Trouble Creating AG Listener: The WSFC Cluster could not bring the Network Name resource We are trying to create an AG Listener and it errors out on us:

It does however seem to create both the DNS entries and the virtual computer object in AD. However, because of this error message SQL backs out the creation of the Listener.

So far we have tried:

Giving the user that sql runs as permission to create objects in AD
Pre-creating the DNS entries and computer object in AD

The only reference I have found to this is at the following connect article, but I can't figure out what the exact fix is from the broken english: http://connect.microsoft.com/SQLServer/feedback/details/686548/denali-ctp3-incorrect-error-message-when-setting-up-listener-with-no-dns-access .

I was able to resolve this issue in my lab.

I setup an Availability Group between two virtual machines in my lab.  I was not able to add the listener as part of the initial setup and also couldn't add it afterwards.

Each server had two network adapters on different sub-nets.  When I disabled the 2nd network adapter on each server, I was able to add the listener successfully. I then re-enabled the 2nd network adapters successfully with no adverse effect on the listener.


How to create a server alias in SQL Server 2012 We have a Windows Server 2012 server running with 2 instances of SQL Server 2012 installed on it - Live and System. Both instances are accepting connections on 1433 & Live is the default instance (1433 set in the ListenAll port).

We have a program that needs to connect to the System instance, however it does not support named instances of SQL Server.

From what I have read, we have 2 options -

install an extra network card, create a separate DNS record for it and have the System instance listen to all connections to 1433 on that card
Create a DNS record pointing to the server and use SQL Alias to point to System instance

We'd prefer to go with option 2 as it means we don't need to setup any more hardware, but I can't get it to work. The network team has set up an extra DNS A record ("programDB") to point to the server.

On the SQL Server I have created an alias using the SQL Server Configuration Manager:

Alias Name="programDB" [I've also tried "programDB.domain.com.au"]
Port No = 1433
Protocol = TCP/IP
Server = ServerName\System

I have set this up in both the 32 and 64 bit configuration

When I connect to programDB from SSMS, it connects me to the Live instance, not the System instance.

Have I miss understood how aliases work, or have I missed something in the setup?

Update: This is what I have set up:

DNS A Record set up to point "programdb" to the IP address of the server
SQL Aliases (x32 & x64):

TCP/IP Settings (port set to 1544):

I've restarted the services a number of times. If I try to connect to "programdb,1544" then it connects to the System instance

Set one of your instances to use a different port. The alias will allow the different port to be used without the client having to worry about it.


What's a good (free) client for MySQL on Windows? I am almost repeating a question done here by another user, but related to MySQL. What do you think is the best free (if any) MySQL client software?

Thank you!

Check this question on Stackoverflow, you will have some idea.

I use the client provide by Mysql itself, it's maybe not the best, but I can get my job done with it


Join on multiple columns with OR I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ID number) in table 1 and 60 columns () in table 2 that must be used as joining keys. So, my code would be something like below:

Select * FROM tbl1 t1 join tbl2 t2 on
t1.col1 = t2.col1 OR
t1.col1 = t2.col2 OR
t1.col1 = t2.col3 OR
...
t1.col5 = t2.col1

and so forth. The combination of 5 and 60 is huge and it kills the server. It also does not sound logical. I was thinking of putting 60 columns as row and increase the number of records for reducing the number of column. However, not yet sure if this is the best solution. Any solution that does not kill server is highly appreciated.

Note: Kindly note that each column from tb1 is to be matched against 10-15 columns in tbl2. For instance, column col1 that contains "Account Number" is joined with col1-10 which hold all potential "Account Number". col1 in no way is joined with email address or some other columns.

I don't think there is a way to get this query to perform well, but the code will be easier to read if you use the IN predicate:

select *
from tbl1 t1
join tbl2 t2
    on t1.col1 in (t2.col1, t2.col2, ...)
    or ...
    or t1.col5 in (t2.col1, ... )


Can I create a semaphore with locked transactions in MySQL? I have a service that requires maintaining a pool of external resources. Therefore I have a table to track the pool. Each row can be used by one user once. My thought was that I can start a transaction, use a lock, try to update row, and see if it errors from having a lock. However it seems my attempt during a lock just makes MySQL wait for the lock to end before making the update happen.

# process 1
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
UPDATE pool SET claimed=1 WHERE id=1;
# (run process 2 below before this commits)
COMMIT;

# process 2
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
# this hangs until above commit
UPDATE pool SET claimed=1 WHERE id=1;
COMMIT;

I know I can lower the lock timeout but that would possibly impact other queries negatively. Is there a way to accomplish what I'm going for via MySQL?

I should note that in reality I will be doing a SELECT on the table for things where claimed=0 ORDER BY RAND() LIMIT 1. If the update throws an exception I plan to do another SELECT to find another row, while the successful path would DELETE the row. Doing the example with id=1 is for simplicity in testing :)

Plan A -- If the task takes only a few seconds,

    START TRANSACTION;
    do all the task (possibly including FOR UPDATE)
    COMMIT;

Plan B -- If the task takes longer than a few seconds.  (Note:  The UPDATEs can probably be done with a single statement, hence no need for a transaction.)

    (with autocommit=ON)
    UPDATE ... to grab an item and announce who has the item
    do the task
    UPDATE ... to release the item

Plan C -- Read about GET_LOCK() and RELEASE_LOCK().  Note that there is a timeout on the GET.

lock_wait_timeout (which applies to Plan A) defaults to 50 seconds.  But I think you should not have transactions that last anywhere near that long.  Hence, Plan B.

Yes, Plan A might include

SELECT .. WHERE claimed=0 ORDER BY RAND() LIMIT 1  FOR UPDATE;

But notice the FOR UPDATE.  And there should be no case for failure.  You have limited the choices to claimed=0.  Caution:  if this is a huge table, the random selection will take time.


Which of these two methods is standard when creating a 1 to many database relationship? If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per customer.

Alternatively I can create multiple addresses and give them all the same AddressId, then in my customer table I can have an AddressId (so you'd do SELECT * FROM Address WHERE Address.AddressId = Customer.AddressId).

Which of these is better, is there some reason why you'd use one over the other, or is one of them just silly?

Alternatively I can create multiple addresses and give them all the same AddressId.

This is not a good idea at all. The AddressId supposes to be a primary key in your Address table.

Foreign Key

Use foreign Key relationship between customer and address tables, if customer may have multiple addresses associated to him/her.

Do enforce foreign key relationship between customer and its addresses by CustomerId being a primary key in Customer table and foreign key in Address table. This will ensure that no orphan records will be left, when customer record is deleted or in-activated.

One-to-many relationship diagram:

In the case if same address can be assigned to different customers, aka many-to-many between customer and addresses, do use a third table CustomerAddress (junction table). Junction table should save pair of (CusomerId, AddressId) and any other related information specific for this combination.

For example, in below diagram we have many to many relationship between Orders and products.

For more references on DB design patterns look here.

Many-to-many relationship diagram:


mysqldump seems to copy excessively I have an old_server with 50 databases.  I want to create new_server with just 3 databases.

When I try using mysqldump, it seems like all the database directories (although they are empty on new_server) and many unnecessary support files for the other 47 tables are being copied.

Is there any way to avoid this (or clean up afterwards, if unavoidable)?  I would like new_server to be as clean as possible, with no traces of those other 47 databases, if at all possible.

I am tempted to delete those files and directories directly from the file system (or at least temporarily move them to /tmp), but I'd like to avoid this if possible.

I think you are backing up all the Databases.You only need to backup those three databases.use mysqldump like:

mysqldump -uuser -ppassword --databases db_name1 db_name2 db_name3 > Three_db_dump.sql

you can see various options of the mysqldump using      mysqldump --help.

In Case If you want to drop the 47 databases which you didn't want on new server If they all are having only MyISAM tables you can easily drop them.It will also reclaim the space.

If the databases have the InnoDB tables then you have to go with another process because only dropping the database doesn't recliam the InnoDB table space.For this follow the link

What is the best way to reduce the size of ibdata in mysql?


MySQL command line interface aliases I am using some commands on mysql client numerous no of times. i want to work smartly with less usage of keyboard. Is there any way to create aliases for mostly used commands like 'show slave status\G','show databases;', 'show tables;' on mysql client just like aliases in bash. Thanks in advance.

Do what I do.  Build PHP web pages with the common stuff.  And have each page do more than the basic.  Don't just show tables, but do show table status and lay it out in a readable table.  And maybe sum up the columns.  And show the Stored Routines.  The cost of a dozen queries to build such a page saves more keystrokes than you concept of aliases.  And it is fast enough to not be a problem if you rarely look at most of the stuff on the page.

The show databases page can have the names hyperlinked to your show table stuff page.  That turns a bunch of keystrokes into a single link.

If you are looking at a table, and you know which columns are "normalized", then automatically do the JOIN to turn ids into values.

For show create table, also do show indexes and look into innodb_table_stats.  Etc.  The Partition info is hidden away; grab it automatically.

For show slave status, have it connect to the Master, too.  And all the Slaves.  And format it so that you can see their states side by side.  Etc.


Why would you need a tablespace? I learned how to create tablespaces, rename it, assign an owner to it, and drop it. But I don't understand why I would even need it. A tablespace is simply a storage area on disk assigned to a postgresql object. It sounds no different from the data directory initialized with initdb. You can assign a data directory anywhere on your disk. So what makes a tablespace different and why would you need a tablespace?

initdb initializes the whole server data directory and every file will be in the same disk. With tablespaces you can distribute objects onto different disks when needed. The reasons usually are performance related. For example, you may want to have some rarely used tables on slower disks and high throughput tables on super fast disks.

Another benefit is to store indexes on separate disks so they have separate I/O from the data.

Naturally also if the volume on which the cluster is on runs out of space, tablespaces can be used to allow the databases to grow to other disks. This requires the tables to be moves to the new tablespace, it will not happen automatically.


How triggers are called when we have two triggers? In a table I have two triggers. When it is called for the first time the first trigger does not fires while the second trigger's got fired.  On the next call both first and second trigger action take place. So in my case when calling the trigger first time the first trigger should be executed then the second one. Any suggestions?

If I am understanding your question correctly, you are looking for the sp_settriggerorder system stored procedure (http://msdn.microsoft.com/en-us/library/ms186762.aspx). Through the used of this stored proc you can set whether a trigger fires first or last (or undefined, and any trigger in between first and last has no guaranteed order).

(Note: this only applies to AFTER triggers, and cannot be used with INSTEAD OF triggers)


Deferrable Foreign Key Constraints What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?

I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon COMMIT anyway.

Are there others?

If it matters, we are thinking about making all foreign keys in a certain table family DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.

TL;DR: What do we have to be aware of when making foreign keys DEFERRABLE INITIALLY DEFERRED?

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

commit might fail. This is usally not expexted by various tools and also by developers
commit might last very long. This might be problem with distributed(XA) transactions.
also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction


How can I profile SQL Azure? I am writing a web site that uses SQL Azure heavily. However, it is painfully slow.

Is there an easy way to profile the live SQL Azure instance?

I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?


Creating SQL Server Performance Baseline Monitoring In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive SQL Server instances.

My thoughts are:

I want to use several DMVs
I want to include a profiler trace (incl. Exec. plans)
I want to include perfmon data

So what I try to achieve is a general performance monitoring startable and stoppable (also scheduable) that returns:

All information required to identify the success of ongoing performance optimization tasks
Couple of aggregated, simple figures that help to visualize the long-term progress .. esp. for management ;-)
Re-executable execution plans within profiler trace to compare individual queue changes and improvements by index optimization tasks

I found couple of information describing the creation of performance baselines. Most of them are either very complicated or focus only on one of the desired performance indicators (mostly perfmon data).

The most matching sample / description was the following: Creating a Performance Baseline for SQL Server

The question is:

Does anyone have experience creating this kind of performance monitor in a quickly doable manner?

More than one year later I want to let everyone know my experience and the final result of this question / topic.

I started out creating things on my own. Initially I followed the Article Collect and store historical SQL Server performance counter data with CMVs by Tim Ford to get something up and extended this with whatever Data I wanted to collect. So once a
per day I run several stored procedures on each Sql Server that collect some specific information from DMVs and stores the results on the local Server inside of a database. This includes index usage, missing indexes, specific log entries like autogrow, server settings, application database settings, fragmentation, job execution, transaction log info, file information, wait stats and more.

Additionally I added Brent Ozar's sp_blitz regulary execution results to this repository to collect additional valuable indications to work, improve and report.

All data is lateron collected from there into a dedicated monitoring Sql Server and this way I create a bundeled store for performance relevant information about all my servers and use this as a base for investigation and reporting.

Then I created excel sheets and also reports using reporting services to analyze and interpret. Some samples:

Also I configured some performance counters monitoring using TYPEPERF inspired by the article "Collecting Performance Data into a SQL Server Table" by Fedor Georgiev.

From my SQL Monitoring instance I trigger typeperf to run and collect a configurable number of samples with a configurable sampleinterval and store the results in my central monitoring db.

This allows me to observer longterm performance values, sample:

After a while of using this to gather baseline information, it tunred out that it is quite a lot of maintenance work that has to be spent on looking at failed jobs, de-buggin procedures (for example in case a DB was taken offline, some scripts failed), maintaining settings after a server was replaced...

Also the database collecting all the records tiself needs maintenance and performance tuning, so additional work comes up to keep the data useful...

What is finally completely missing is the ability to look at things that happen live. In Best Case, I will be able to tell what was possibly going on the next day after the data collectors have ran. Also all the details are missing. I do not have access to deadlock graphs, I can't look at query plans of queries that were running in a suspicious timeframe....

All of that made me going to charge management to spend money for a prefessional solution I am not able to create on my own.

The final choice was to buy SentryOne because in comparison to others it is convincing and delivers a lot of information required to identify our pain points.

As a final conclusion I would advice anyone looking for answers to similar question not to try creating things on their own as long as you don't have a small and basically healthy environment in place. If you have couple of systems and a lot of problems, better immedeately go for a professional solution and use the assistence of the vendor on your problems instead of spending a lot of time and money to create somehing less useful. However, this route was still very interesting and made me learn a lot I dont want to miss.

I hope you find this useful once you ran into this question thread.

EDIT April 20, 2017:
Brent Ozar recently posted the following article on facebook that is kind of a simiar approach taken by the SQL Tiger Team: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/


User, host, and privileges In a MySQL 5.6 database I found one user 'foo'@'10.0.0.2' with ALL PRIVILEGES.

I also discovered an user 'foo'@'10.0.0.3' granted only USAGE privileges.  Does this second user definition make sense?

Thanks.

Edit: for the sake of clarity, the MySQL database is on a machine which is neither 10.0.0.2 nor 10.0.0.3.

USAGE = no privilege

https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_usage

Now you have to options, either delete the user or grant all privileges to the user, which ever make sense to you

CASE 1: If you want to remove 'foo'@'10.0.0.3'

REVOKE USAGE on *.* from 'foo'@'10.0.0.3' ;

will never work as there is no grant called USAGE So use

DROP USER 'foo'@'10.0.0.3';

CASE 2: If you want to grant all privileges 'foo'@'10.0.0.3'

GRANT ALL PRIVILEGES ON *.* TO 'foo'@'10.0.0.3';
FLUSH PRIVILEGES;


How can I create DB2 objects with Linux SUDOer authority? All,

I am relatively new to Red hat linux and I have SUDOer authority but unable to create DB2 database or other objects. How can I accomplish this task? Please give examples.

Also, is there any known freeware or trial tool for converting mainframe databases and associated objects to DB2 LUW specifically on linux box?

Thank you

Thanks in advance for your response.

Your OS authorities don't matter to DB2, which has its own authorization mechanism, although it is based on the OS authentication. In other words, you must have a login ID in the OS, which, if you are successfully authenticated, will be used to determine what authorizations you have from the DB2 point of view. You can read more about the model here: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.sec.doc/doc/c0021804.html

At a minimum after installing DB2 and creating an instance you will have the instance owner user that has sufficient authority to create a database. The creator of the database, in this case the instance owner, automatically gets the SECADM (security administrator) and DBADM (database administrator) authorities over that database, which allow that user to create objects in the database and grant various privileges to other users.

With your sudoer  rights in the OS you can su -l to the instance owner user (might be db2inst1 as this is the default) and perform whatever job you need.

As for the database migration tools, consider the IBM Database Conversion Workbench, which is a freely available plugin for IBM Data Studio that is also free.


Concatenate text and tochar(): Why is there a space between the two? I want to construct an ID from a textual prefix and a number (more info here).

For example, I want to concatenate ABC with 1, while padding the 1 to 6 digits: ABC000001

However, when I try this, it puts a space between the two:

SELECT
    'ABC' || to_char('1','000000') AS ID
FROM
    DUAL;

Result: ABC 000001

Why does it put a space in there?

Because to_char() pads the result with spaces (I never understood why).

You need to use the "fill mode":

'ABC' || to_char('1','FM000000')


Different Results between stored procedure and UDF I am getting different results between my stored procedure and my UDF.

The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, then replaces the NULLs with '00:00:00' so that I can sum the amounts. Then takes that date, and does a DATEDIFF on it to give me the total hours worked.

If I input the values ('John Doe', '04/05/2013', '04/16/2013'), the stored procedure is returning the answer I am looking for, the SVF is not. Where the stored procedure would result in "23" for hours worked, the SVF returns "1900-01-24 00:00:00".

I realize that it is with what I am returning in the SVF, but I can't figure it out. I have tried to return TIME(0), VARCHAR(5). It is as if the SVF isn't doing the DATEDIFF bit.

I am working with SQL Server 2012 (11.0.3000)

Below is the SVF and the stored procedure.

The SVF

ALTER FUNCTION [dbo].[fnFindHoursWorked]
    (@Name varchar(50)
     ,@Start_Date DATE = NULL
     ,@End_Date DATE = NULL)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    DECLARE @HoursWorked DATETIME

    -- Add the T-SQL statements to compute the return value here
    /** create a table variable to store the results of the query **/
    DECLARE @Duty TABLE (Time_on_Duty TIME(0))

    INSERT INTO @Duty(Time_on_Duty)

    /** The actual query that grabs the data from the  server **/
    SELECT
        CAST(UL.[Date_Time] - LAG(UL.[Date_Time], 1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME(0)) AS 'Time On Duty'
    FROM [LinkedServer].[database].dbo.[tablename] AS UL
    WHERE UL.[Department] = 'Department'
      AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
      AND (UL.[Name] = @Name)
      AND ((CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
            )

    /** Setting all of the NULLS in the table variable to a value on which we can do math **/
    UPDATE @Duty
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    /** The select statement to grab the total hours worked for the date range **/
    SET @HoursWorked = (SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty' FROM @Duty)

    -- Return the result of the function
    RETURN @HoursWorked
END

Here's the stored procedure:

ALTER PROCEDURE [dbo].[rptTotal_Time_On_duty]
    @Name varchar(50)
    ,@Start_Date DATE = NULL
    ,@End_Date DATE = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID (N'tempdb..#Duty', N'U') IS NOT NULL
       TRUNCATE TABLE #Duty
    ELSE
       CREATE TABLE #Duty (Time_on_Duty TIME)

    INSERT INTO #Duty(Time_on_Duty)
        SELECT
            CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On Duty'
        FROM [LinkedServer].[database].dbo.[tablename] AS UL
        WHERE UL.[Department] = 'Department'
          AND ((UL.[Action] = 'OnDuty' OR UL.[Action] = 'Login') OR UL.[Action] = 'OffDuty')
          AND (UL.[Name] = @Name)
          AND (
           (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
        OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
        )

    UPDATE #Duty
    SET Time_on_Duty = '00:00:00'
    WHERE Time_on_Duty IS NULL

    SELECT DATEDIFF(hour,'1900-01-01 00:00:00',CAST(SUM(CAST(CAST(Time_on_Duty AS DATETIME) AS FLOAT)) AS DATETIME))  AS 'Time_On_Duty'
    FROM #Duty

END

DATEDIFF return the result in int. Change the @HoursWorked data type in your scalar value function to int instead of datetime.


Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL I am trying to create a new table new_table from t1 by comparing primary key values of another table t2

ie,
      1) created table new_table

  2) gave the query

insert into new_table select a.* from t1 as a join t2 as b on a.primary_key=b.primary_key.

but after the execution i got a message ( 0 rows affected )

please specify the reason for this.

I'd have to assume that no matching values exist in the tables' respective primary key columns.

mysql> create database testing;
Query OK, 1 row affected (0.02 sec)

mysql> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bar (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table baz (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into bar values (4), (5), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bar values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>


Repeat total count value I have a table called paths that looks like this:

id  parent_id
--  ---------
 1       null
 2          1
 3          2
 4          3
 5          4
 6          5
 7          5
 8          5
 9          5
10          5
11          1
12         11
13         12
14          1
15          2
16         15
17          3
18          2
19         18
20         19

I have the query:

SELECT count (id) as count_id, id FROM paths where parent_id=5 group by id

It returns me:

count_id  id
--------  --
1          6
1          8
1          9
1          7
1         10

But I want to get the following:

total_count  id  parent_id
-----------  --  ---------
5             6          5
5             8          5
5             9          5
5             7          5
5            10          5

Since the total count is 5, I want to show 5 in every column.
What I am doing is finding all nodes whose parent id is the specified number, count the nodes and return every found node along with that total number.

If I found nothing, I'd like to have

total_count  id    parent_id
-----------  ----  ---------
0            null       null

Is there a way to do it?
I use PostgreSQL 9.5.

The GROUP BY id is useless in the query, since there is only one row for every id (it appears to be the primary key of the table).

What you need a count over the whole result. This is easy using a window aggregate:

SELECT count(*) OVER () AS total_count,
       id, parent_id
FROM paths
WHERE parent_id = 5 ;

The last requirement is rather weird but you could have that, too, with a more complicated construct:

SELECT count(p.id) OVER () AS total_count,
       p.id, p.parent_id
FROM (SELECT 1) AS dummy
  LEFT JOIN paths AS p
    ON p.parent_id = 5 ;


SSRS and Power BI on same server I understand that as of the 2016 versions it is now possible to run SSRS and Power BI on the same server. I would like to know if anyone is currently running this combination and what are the pros and cons of doing so.

I assume you mean Power BI Report Server.
It's useful if you want to test or develop using PBIRS without risking your SSRS installation. As far as i can tell PBIRS can do everything SSRS can and more. You can still deploy SSRS reports to PBIRS. In certain scenarios you may want to have 2 instances of a reports server. The argument would be the same for having 2 instances of SSRS. Separation, security, scalabiliy.
The downside is competition for resources and management/maintenance overhead. I think you really need to have a strong argument for maintaining both. My reccomendation is: Test it, if it does everything you need then replace SSRS with PBIRS.
If you're insterested in how to do that I discuss the steps here: https://dba.stackexchange.com/a/219134/41928


MYSQL only Show columns which allow null I need to be able to pull the column names which allow a NULL value,  I know that

show columns from TABLE

Will give show me the table properties and whether or not the column allows null values, but is there a way to just return ONLY the columnnames which allow null.

show columns from TABLE where Null = 'YES' doesn't work, but it explains what I need to accomplish.

And of course it's easy to just pull everything and sort it out later on, but if there IS a way to do what I'm asking, I'd like to learn it.

Yes you can do it by using the information_schema database..

use the Query

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='db name'
  AND TABLE_NAME='table Name'
  AND IS_NULLABLE='YES';


SQL Server 2012 doesn't use enough memory Under heavy Load, my SQL Server database is executing queries really slowly. I have 96 GB of RAM and my CPU is high (100%) but my physical memory usage is at 9%. I looked at the SQL Server memory usage which is in the screenshots below.  I enabled Locked In Pages but it didn't seem to help. I'm wondering if there is anything I can do in SQL Server to utilize more memory.

Version: SQL Server 2012 Web Edition SP3 (x64)
OS: Windows Server 2012 R2 (x64)

max server memory is set to the default. It's a web garden for one site. My database is 2 GB. I thought SQL Server would use more memory under a heavy load with more RAM, scaling up for the number of users/queries.

Be sure that memory is the bottleneck

Here is a query that will tell you for every session, how much memory is being used versus the ideal amount of memory the optimiser would like to assign to the query. If the latter is higher than the former, then memory is a (but not necessarily the only) issue for you.

SELECT  s.session_id
        , r.granted_query_memory * 8 AS memory_used_kb
        , a.ideal_memory_kb
FROM    sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_query_memory_grants a ON a.session_id = s.session_id


Difficulty Designing A Product Database The image below is my ham-fisted attempt to design a database of cabinets. I'm new to this and this is simply an exercise to help me understand database structure.

My goal is not to create an inventory/order database that relates to client purchases. I want to create a database that would allow me to populate products based on style, material, joinery, or etc... In other words, if a customer visits the website and selects to view cabinets of a certain category then all the cabinets associated with that category will populate in an array. It will display an image and beneath that a title. For example:

From what I've researched attributes should define the columns of the table, but many of the cabinets have multivalued attributes. I'm starting to believe I've overcomplicated the design. I added a few text blocks to hopefully clarify the purpose of the table. Can someone with experience provide some constructive feedback?  Thanks!

I can only repeat what I tell everyone - Data Model Resource Book Volume 1 is maybe half about that topic and it is a serious goldmine of ideas and complications.

Seriously, depending what you want to cover that is a brutal topic. Some things are sized (3 x  2meter lone,  5x1.5 meter long), some have colors, some sizes, some colors AND sizes.

Price may depend on attribute, volume combined dimension (total meters). World of pain.

The question is how much of that you NEED - and what yo can cut.

I suggest reading the book for also in depth discussions about the topic.

Sadly, that is all I can say - without a requirement, a review of your schema is moot (did you forget something? How can I know... we only can check what is possible, not what is wanted, in product complexity).


Cannot create a database connection SQL Developer I will try to explain the current situation in the best possible way.

Well, I got a VM with SQL Developer already installed but could not run properly. After some reasearch, I solve the problem related with environment variables.

Currently, I need to create a database connection but unsuccessfully. I do not know what are the credentials that I have to use it. I can log in on sqlplus as sysdba.

I already did the select * from all_users but I am still stuck on this. Is there anyway to discover the right credentials to create the connection?

Thanks anyway.

I was putting the wrong service name. You can check yours on tnsnames.ora.


Load test with databases. Best practices , tools I need to design a way to stress a clon of my production databases.
At the moment, I'm using mysql percona xtradb with tree nodes running in google cloud.

I'm using haproxy as load database balancer.

All backend microservices in my company are configured to connect through haproxy to the node1 for writes and to the nodes 2 and 3 for select.

I want to make my database three times bigger.

I have cloned my production database using backups in other google instances now I'm going to populate this database inserting more records.

I want to stress my new database x3 bigger, using in my load test the top queries with the poor performance that I have found using my tuning tool (Percona monitoring and management PMM)

I have thinking in three options:

Use mysqlslap
Use Apache JMeter using jdbc module
Design my own stress tool using python with flask to expose a API with my custom loadTest and then I can use JMeter or soapUi to stress with different multithread profiles my API and it runs the queries.

I would like to have feedback on how to approach this or to ask if anybody knows other tools to stress database in a multithread environment.

Thanks.

Take a snapshot of your production database and at the same time enable the general log. Record 24 hours of a general log on what is a fairly typical day.

Restore the data to clean server.

Write a few lines of your favourite $scripting_language to parse out the general log and split it up into n files where n is at least the number of CPU threads you have in the server. Also, separate SELECTs, INSERTs, UPDATEs, and DELETEs into separate sets of files.

Replay them in parallel, SELECTs only, to prime the caches. Then do it again and time the 2nd run.

Now you have a baseline to measure everything else against. From there on, triple up the number of rows in the table by adding more rows, and replay it again to measure the performance.

No generic benchmark will ever come close to producing as meaningful a benchmark number as this method.


How to re-include excluded folder in SSDT-BI / Visual Studio 2013 database project? I've selected "Excluded from project" on a folder with tables (database was imported first into project). Now I would like to re-include this folder into the project. In the "Add Existing Item" option it's not possible. Only files can be added like that. Do you know how to do that ? Is it possible or should I re-create the folder and add the files there again manually ?

This is actually fairly easy to do. Do not re-create the folder.

With Visual Studio open and the solution loaded, do the following:

View the Solution Explorer (shows on the right side).
Select either the project or any of the folders or items within the project.
In the top button bar within Solution Explorer, click on the >> button on the far right. Three or four new buttons should appear below your cursor (depending on what type of item you had selected).
The far left button in the new set that appeared when you clicked the >> is for Show All Files. Click that. You should now see folders and files, etc that are dotted outline icons instead of solid shapes.
Right click on the folder that was previously excluded from the project.
Select Include In Project.
In the top button bar within Solution Explorer, click on the >> button on the far right. Three or four new buttons should appear below your cursor (depending on what type of item you had selected).
The far left button in the new set that appeared when you clicked the >> should already be selected. Click that to re-hide all of the folders and files, etc with the dotted outline icons.


MongoDB replica index filled hard drive stuck in fatal I have a 3 server replica set deployed by Googles 1 Click Deploy functionality, the problem is I created an index on a particularly large collection (100,000,000 documents) and it consumed the whole hard drive and caused Mongo to crash and stay crashed.

I have deleted a 30GB log file and freed up some other space and tried to restart the mongo process on both the primary and secondary servers but the secondary keeps falling into fatal even after getting it to rollback.

I've run rs.reconfig() and it seemed to start the rollback process but it just fell back into FATAL and now it won't budge unless I restart the process and it goes in circles.

To make matters worse, the data (due to connection/training issues) sits only on this busted instance and is not spread across the replica.

I've tried to also run rs.slaveOk() and remove the offending index but I can't query without getting told I can't and obviously when it's in ROLLBACK I can't query for anything because of the lock.

Is there any way I can remove the offending index, repair and resync to fix this while it's in this state?

Thanks.

If your data exist on a node then this node is named Primary from now on and the other 2 secondaries.

1) Start the Primary as standalone and drop the index.

2) Take a backup of your data just in case

3) Stop secondaries and delete their data directory.

4) Start the Primary as a member of the replica set.

5) Start the first secondary. This will cause an initial sync of the data, when the member join the set as secondary start the other secondary.


Does InnoDB store row counts in secondary index B-Tree non-leaf nodes? Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes?

If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate index exists).

I am not 100% sure about the first part, but I would say NO - if it did, it would have to update the counts all the way to root on each write, which would mean writing more pages to disk and syncing that. When you add/remove the leaf node, then in the good case you just need to write that one page to disk, and with this you would have to update all those "parents" - say 2-5 more pages depending on the number of rows in the table.

The other thing is that InnoDB cannot use that information anyway in queries. When you run the COUNT, you run it in some transaction, that usually means "repeatable reads" mode (but similar reasons will apply to other modes). So the query has to check the visibility of each row/record in the current transaction scope. And you cannot keep separate count per active transaction in the b-tree.


SQL Server memory issue As soon as I start SQL Server with no application querying, in the task manager it starts to consume memory very fast till it consumes around 15GB.
How can I resolve this issue?

To determine which queries are pulling objects into the buffer, use a server side trace or a tool like SQL Server Profiler. This will capture each query that is being ran on the database server, as it is being executed.

You didn't say which version of SQL Server, but if 2005 or higher, I'd strongly recommend reading Aaron Bertrands Determine SQL Server memory use by database and object post. The first query returns the databases which have objects in the buffer, and the number of pages and space they are consuming. Find the top database, and use the second query to determine which of the databases objects are taking up most of the buffer.

If you are on SQL Server 2008 or higher AND CPU is not a bottleneck AND you have memory pressure and can't get more memory added, I'd also review Edwin Sarmiento's Implementing Data Compression in SQL Server 2008. You may try data compression on a few of the top objects from the memory by object query. Compressed data takes up less space on both the storage substem and in the memory buffer, so you can fit more data pages in memory. When more data pages fit in memory, it reduces the number of reads from / writes to disk. As Edwin describes: "Data compression reduces the storage costs and increases query performance by reducing I/O and increasing buffer-hit rates." The tradeoff is a slightly higher CPU Utilization.


Why is SQL Server memory showing in task manager I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager.

However I have recently been doing some testing on SQL Server that someone else has setup.

I am expecting to see sqlservr.exe running at about 40mb memory. However, Task manager is displaying that over 4Gb of memory is being used.

Is there a setting in SQL Server that is causing the task manager to report this inaccurately? Or is there something going wrong in the background of my SQL Server that is causing it to balloon?

The number reported by Task Manager is not inaccurate exactly; you just have to understand what the number is telling you.

If you expand the Task Manager column so you can see the whole title, you'll see it says: Memory (Private Working Set). The working set does not include any memory that is being accessed via Address Windowing Extensions (AWE).

AWE is a technology API that allows a 32-bit process to access more memory than its 4GB virtual address space, by mapping and remapping a small 'window' of virtual address space onto a larger physical memory space.

AWE is how 32-bit SQL Server is able to access more than 4GB of memory, and the same mechanism is used in 64-bit SQL Server to implement the 'locked pages' feature.

So, Task Manager will show only a relatively small amount of memory in use for:

32-bit SQL Server using AWE to access memory; or
64-bit SQL Server using locked pages

In your case, with Task Manager reporting over 4GB in use, it seems likely the SQL Server you were looking at was a 64-bit installation that is not using locked pages. Instead of using Task Manager, use the Performance Monitor SQL Server instance counter Total Server Memory, or query the sys.dm_os_process_memory DMV in SQL Server 2008 and later.

Reference and further reading: Fun with Locked Pages, AWE, Task Manager, and the Working Set by Microsoft Customer Service and Support (CSS) SQL Support.


Transfer data from the cloud to a local database I have an SQL Server 2016 Azure database in the cloud called XCLOUD, with many tables big and small, and a local SQL Server 2016 database with the same tables and data called XLOCAL.

In all databases there are neither PK, nor FK.

My goal is to keep XLOCAL updated with all the changes in XCLOUD, minimizing network traffic and keeping XLOCAL available all the time. Performances on Azure are not a problem: once XCLOUD is filled, the server does nothing else until the next day.

A daily process on the Azure server truncates all the tables in XCLOUD and fills them again with almost the same data. I don't have control on this process: if I had, I would change it to only INSERT/UPDATE/DELETE and then I would use log shipping, but this is not possible.

One idea I had is to create a new database from XCLOUD1 on the Azure server: when the process filling XCLOUD finishes, I would somehow compare the differences between XCLOUD and XCLOUD1, update XCLOUD1 with MERGE statetements as needed, then use log shipping from XCLOUD1 to XLOCAL. But I would need to code the MERGE statements one by one, unless you can recommend some tool for this task.

What would you do?

This is returns column and table names you can use to build up the statements

select column_name, table_name, ORDINAL_POSITION
from information_schema.columns
order by table_name, ordinal_position

I would do it in this order: delete, update, insert
Merge is known to have some issues


High Service Broker queue My production database suffers from a large Service Broker message queue. It is around 2 million. I would like to decrease it as small as possible.

I have tried dbcc dbreindex('sys.queue_messages_245995913') from the DAC connection, based on the advice given by Remus Rusanu here.

But when I look at the queue properties, the queue size remains the same.

TRY TOP 10000 INSTEAD of 1 to quickly read messages from Queue that will clean the queue faster

WAITFOR ( RECEIVE TOP ( 10000)
                   CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
                  FROM dbo.[EventNotificationQueue]

> --into @message_body

                  ), TIMEOUT 1000


Data Collector Component Name Doesn't Change After Updating SQL Server Name I cloned a SQL Server, and changed the name of the machine and SQL Server, but the old name continues to show up as a component name in the disk usage data collector, which I believe is what's causing that job to fail.

This is part of the error in the job history for that task that includes the old name:

SSIS error. Component name: TSQLQuery_OLD_NAME_MSSQL10_50_MSSQLSERVER_{}_1_Collect

I have already dropped the old name using sp_dropserver and added the new server name using sp_addserver. I verified that SELECT SERVERPROPERTY('MachineName') brings up the correct information, as does sp_helpserver. However, SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

How do I change the component name?

I cloned an MS SQL server, and changed the name of the machine and SQL
  server, but the old name continues to show up as a component name in
  the disk usage data collector, which I believe is what's causing that
  job to fail

  However, SELECT @@SERVERNAME brings up a NULL value, despite
  restarting the server.

  How do I change the component name?

Give this a shot since you state SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

1. Run the below with the local as the second argument. . .

TSQL

SP_DROPSERVER
GO
SP_ADDSERVER 'NewServerName', local
GO

2. Restart SQL Server (if you can restart the server OS meaning power cycle it)

3. Run the below once it's back up and confirm . . .

TSQL

SELECT @@SERVERNAME
SELECT SERVERPROPERTY('ServerName')

4. If the values are correct, then rerun your SSIS process and see if that resolves it as well.

SP_ADDSERVER

  [ @local = ] 'LOCAL' Specifies that the server that is being added as a local server. @local is varchar(10), with a default of NULL.
  Specifying @local as LOCAL defines @server as the name of the local
  server and causes the @@SERVERNAME function to return the value of
  server.

  SQL Server Setup sets this variable to the computer name during
  installation. By default, the computer name is the way users connect
  to an instance of SQL Server without requiring additional
  configuration.

  The local definition takes effect only after the Database Engine is
  restarted. Only one local server can be defined in each instance of
  the Database Engine.


How do I pass or fail a job in SQL Server agent based on a return value from a stored procedure? What do I need to do in order to execute a step in a SQL Server 2012 agent job that returns a bit value back? Basically, I want to fail the job or pass to the next step based on the results from the execution of the stored proc.

Evaluate the result from the proc (with an IF statement) and then if you want it to fail the job execute a THROW command. See this article.


Varchar storage and performance I have a simple query, It might be answered before but I didn't find the precise answer for the same.

My question is:

I created a table using JPA through entity. Now I shows string fields as varchar(255). Now I'm wondering that what will happen when I'll save some data in that field of size let say 10? Will it save that data in 10 bytes or it will use 255 bytes?

Any clarification will be appreciated.

I thought in MySql up-to 255 chars with a varchar it is the number of characters you store plus 1 for length. So if you  put a value that's 10 characters long, that'll be 11 bytes on disk.

UPDATE
Found a link to the documentation to back that up. First table demonstrates the difference between char and varchar: https://dev.mysql.com/doc/refman/8.0/en/char.html


Help with best practice of merging databases Bad news, our website server (Windows 2003) crashed because of dead RAID controller.

Luckily few hours later our backup server was up and website was live again.

Hopefully tomorrow our original server will be fixed and I'm looking for the best practice to merge our data (SQL Server 2005).

This is the situation right now:

Original server has backed up data till 16th 01:30AM (night.bak)
Original server has data which is not backed up till 17th 00:30AM
so basically there is 23 hours missing on the night.bak

I restored the night.bak to the temporary server and since 17th 03:00 new data is being added to the temporary server.

Tomorrow I wish to take data from the temporary server (17th 03:00 till 19th) and put it back on the original server.

I believe I can't make differential backup on the temporary server and restore it on the original server because last backup on original server has timestamp of 16th 01:30AM but I don't really know so I'm asking here.

My main concern is to preserve data of joined tables that share index keys.

I don't know how to reply to the answer so I'm replying here :

I'm testing Red-Gate's SQL Data Compare (which is by the way 14 days trial and not 30) and it seems good to add / update / delete rows very easily.

But this is the problem:

old database has 120 records on tableA (primary key 1-120) when I left it
new temp database doesn't have all 120 records because only 100 records were backed up so, it has only 100 records (primary key 1-100)

Since we continue using the temp database it now has 140 records (primary key 1-100 from original database and 101-140 from temp database).

The Compare would want to insert 40 new records to the tableA but it cannot use 101-120 key because it's already exists on the original database so I guess it will try to update and destroy them.

And anyway, it cannot insert with correct keys because, for example, I have table tbl_users (code(index), firstname, email) and table tbl_priceoffers (code(index), usercode(from tbl_users), price).

Red-Gate generate script to insert tbl_priceoffers before tbl_users
but even if it was the correct order (I can edit), it cannot insert tbl_priceoffers row without having @@identity from the recently inserted tbl_users row.

Your best bet is to use Redgate's data compare to compare and insert data in the database your application is pointing to.

I have used this method in similiar situation and trust me ... this tool is a life saver.

You can dowload a 30 day full trial version and then purchase it if it suits your needs.


MySQL Range partitioning vs Hash partitioning We have one large table with 100 million records and its size is 100 GB and data is continuously growing day by day. This table has 30 partitions with RANGE by client_id(table's column). One client_id is having minimum 1 million records.

For solution we have break this large table into 30 small tables with group of client_id data in each table. Now we want to do partition in each table.

So which partition would work for us best: Range or Hash?

We don't know which table has which set of client_id.

Thanks

Short answer: Neither.

Long answer:

Partitioning is not a panacea.  It does not automatically provide performance.  In fact, it sometimes slows down processing.

I have never see a use case where BY HASH helped.  I have seen a few use cases for BY RANGE.  Off hand, it does not sound like your use case will benefit.

Please provide SHOW CREATE TABLE and a few of the critical SELECTs.  With those, I will either

Explain why partitioning is useless.  Or...
Explain how to benefit from partitioning for those queries.

In either case, I will probably suggest index (including PK) changes.  Proper indexes is where performance usually comes from.

In another direction...  Is this some form of "multi-tenant" app?  Will it eventually out-grow the server?  If so, Sharding may be in your future.  This is when you scatter "clients" (or whatever) across multiple servers.  But, alas, MySQL does not have any built-in support of such.  There are 3rd party packages (eg, Spider) that do.


Postgresql error: failed to re-find parent key in index I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently the VM ran out of disk space (resulting in no space left on device errors showing up in my logs).

I cleared the space on the device and everything came back online.

But now, for a minority of my requests, I've started receiving a django.db.utils:DatabaseError alert, with the description failed to re-find parent key in index "links_grouptraffic_time" for split pages 24582/24583. links_grouptraffic is one of my data models, and time is an attribute within that data model.

Can someone explain the background of what this means, and a remedy for this problem? Thanks in advance!

Your database is likely corrupted, especially the indexes. You can rebuild all indexes and check all tables on a database by:

REINDEX SYSTEM; -- run once
REINDEX DATABASE <your dbname>; -- run for each database
VACUUM (FULL VERBOSE ANALYZE); -- also run for each database after reindexing


Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? My code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

The error:

  Msg 8101, Level 16, State 1, Line 4

  An explicit value for the identity column in table
  'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
  is used and IDENTITY_INSERT is ON.

What gives?

Add a column list to your code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
    (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

GO

Basically, a column list is when you explicitly state what columns you're inserting into.

Just remember that when you set IDENTITY_INSERT ON, you must specify in your insert statement the value for the column that is the identity
(in your case the column ColumnWithIdentity).
If IDENTITY_INSERT OFF, SQL Server sets the first free value for the identity column, and so you must not add the ColumnWithIdentity field in the SELECT part of your INSERT.


Will transactional replication break if there is a heavy update/delete query run on the publisher DB For these specifications:

Publisher: (prod OLTP DB server) SQL Server 2005, database size : 220 GB also acts as the distributor
Subscriber (Prod reporting DB server): SQL Server 2005, DB gets replicated and is currently in sync.

Now, as per the yearly activity there would  be a script run on OLTP publisher DB to delete more than 3516009 records.

Please suggest if this breaks transaction replication? Or is there a way I can prevent one because setting that up is tedious?

Also, something similar was done 2 years back and we only have the error as

  The process could not execute 'sp_replcmds' on 'xxxxxx'." "Status: 2,
  code: 0, text: 'Timeout expired'" "A time out occurred while waiting
  for memory resources to execute the query." "Agent 'xxx' is retrying
  after an error. 0 retries attempted. See agent job history in the Jobs
  folder for more details." "The step was cancelled (stopped) as the
  result of a stop job request""

And the fix that was done at that time was to increase the query time-out for log reader agent and was set from default of 1800 to a max 65333 secs.

Since the DB has grown over last 2 years we are wondering, if above fix will still be good or should we take any measures?

Also, in case replication breaks, what can be the fastest way to bring it back because setting the whole thing up (especially SQL Server 2005) will bring a big outage on reporting server?

Sounds like you were attempting to perform the delete operation in a single transaction.  This being the case, the answer is "Yes, it will most likely break."

Even without replication, it's a bad idea to do such a large operation in a single batch.  You would be better off looping a smaller number of deletes - it's faster and less of a performance issue.  If you do this, the answer is, "No, it shouldn't be a problem."

Here's an example

Declare @keepGoing bit
set @keepGoing = 1

while @keepGoing =1
begin

    if (select top 1 from someTable) is null
        set @keepGoing = 0
    else
        delete sometable where ID in (select top 10000 ID from sometable)

end


Is it better to omit associative tables in displaying many to many relationship Let's say I have 2 entities Student and Class with many to many relationships. Usually, in the textbook, it is recommended that we create another associative table (maybe called Enrollment) to convert many-to-many relationships becoming 2 one-to-many relationships.

So this design above is correct and I have no problem with it.
However, I also thinking of a more simple design like this
Student
id name class_id
1  Jake 1
2  Jake 2
3  John 1

Class
id name
1  Math
2  English
3  Physics

And I think it can also work fine without even creating the third tables. (and only Student table has the foreign key)
So my question is what are the pros and cons of the second method ( without associate tables). Is there any particular case that making the text-book method (1st solution) better than the 2nd solution and vice versa. Thanks

Your proposed Student table is the same as the picture's associative table, only using the natural key (Student Name) instead of the surrogate key (Student ID) as the primary key. (As an aside, getting a natural key that uniquely identifies humans is one of the notoriously hard tasks in data.)
The downside of your proposed model is that the student can have no existence outside of their participation in a class. The student comes into existence (as far as this model is concerned) only when she enrolls in a class. Should she un-enroll she ceases to participate in this model in any way whatsoever. I know this is a made-up teaching example, but in real life we'd have students who are not currently enrolled in any class - say from the time they're accepted until they show up days or months later and enroll. Further each student will have many more attributes (phone number, next of kin, etc.) In your proposed solution these must be stored redundantly on every row. This breaks second normal form, risking data update anomalies.


Postgres Error: must appear in the GROUP BY clause or be used in an aggregate function I'm a novice when it comes to Postgres, and I realize similar questions have been asked before, however, I'm struggling to make this query work:

SELECT    p_search.id, count(kia.image_id) AS count
FROM      (SELECT id,
                  keyword,
                  to_tsvector('english',keyword) AS tskw
          FROM keyword) AS p_search
LEFT JOIN keyword_image_assoc AS kia
ON        kia.keyword_id = p_search.id
WHERE     p_search.tskw @@ plainto_tsquery('english','resident life')
OR        p_search.tskw @@ plainto_tsquery('english','wofford')
GROUP BY  kia.image_id
ORDER BY  count desc

For context, I have three tables:

image which has an id as the primary key
keyword which has id as the primary key and keyword which is a string representing a keyword
keyword_image_assoc which has an id, image_id and keyword_id which is used to associate images and keywords canonically.

If I add p_search.id to the GROUP BY clause, the results aren't correct - count is always 1. Is there a way to group the results so the image_ids have the count of the number of matching keywords?

I'm looking for two columns count - the number of matching keywords and image_id the id of the corresponding image. Thanks!

Have a look at Postgres docs about GROUP BY:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

According to this you cannot reference a field on the SELECT statement if it doesn't appear on the GROUP BY clause or without using an aggregated function.

In your example, you're referencing p_search.id but it doesn't appear on the GROUP BY clause. You could try to change it by kia.image_id


SSAS - Applying Conversion Rates for Units of Measure So I've been playing with this for the last day or so, and haven't been able to get an closer.

I have a SSAS cube that has a measure group which displays total execution times for events in seconds. What I'm looking to do is give our users the option to select which time unit they want to display their results in (ie. minutes, hours, days, weeks, etc.).

On the surface, it seems similar to performing currency conversion. So using AdventureWorks as my template, I created 2 additional tables.

CREATE TABLE [Dim_DurationUnits]
(
    [DurationUnit_SK] INT NOT NULL,
    [DurationName] VARCHAR(50) NOT NULL,
    CONSTRAINT [PK_Dim_DurationUnits] PRIMARY KEY CLUSTERED ([DurationUnit_SK] ASC)
)
CREATE TABLE [Fact_DurationConversions]
(
    [DurationConversion_SK] INT NOT NULL,
    [DurationUnit_SK] INT NOT NULL,
    [DurationConversionValue] DECIMAL(18, 7) NOT NULL,
    CONSTRAINT [PK_Fact_DurationConversions] PRIMARY KEY CLUSTERED ([DurationConversion_SK] ASC),
    CONSTRAINT [FK_Dim_DurationTypes_Fact_DurationConversions] FOREIGN KEY ([DurationUnit_SK]) REFERENCES [Dim_DurationUnits]([DurationUnit_SK])
)
INSERT INTO [Dim_DurationUnits] ([DurationUnit_SK], [DurationName])
SELECT [DurationUnit_SK], [DurationName]
FROM (
    SELECT 1 [DurationUnit_SK], 'Seconds' [DurationName]
    UNION ALL
    SELECT 2 [DurationUnit_SK], 'Minutes' [DurationName]
    UNION ALL
    SELECT 3 [DurationUnit_SK], 'Hours' [DurationName]
    UNION ALL
    SELECT 4 [DurationUnit_SK], 'Days' [DurationName]
    UNION ALL
    SELECT 5 [DurationUnit_SK], 'Weeks' [DurationName]
    UNION ALL
    SELECT 6 [DurationUnit_SK], 'Months (30 days)' [DurationName]
) A

INSERT INTO [Fact_DurationConversions] ([DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue])
SELECT [DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue]
FROM (
    SELECT 1 [DurationConversion_SK], 1 [DurationUnit_SK], 1 [DurationConversionValue]  -- Seconds
    UNION ALL
    SELECT 2 [DurationConversion_SK], 1 [DurationUnit_SK], 60 [DurationConversionValue]  -- Minutes
    UNION ALL
    SELECT 3 [DurationConversion_SK], 1 [DurationUnit_SK], 60 * 60 [DurationConversionValue]  -- Hours
    UNION ALL
    SELECT 4 [DurationConversion_SK], 1 [DurationUnit_SK], 24 * 60 * 60 [DurationConversionValue]  -- Days
    UNION ALL
    SELECT 5 [DurationConversion_SK], 1 [DurationUnit_SK], 7 * 24 * 60 * 60 [DurationConversionValue]  -- Weeks
    UNION ALL
    SELECT 6 [DurationConversion_SK], 1 [DurationUnit_SK], 30 * 7 * 24 * 60 * 60 [DurationConversionValue]  -- Months
) A

With that complete, I added the duration unit dimension and the conversion rate measure group to my cube, and updated my total duration measure with a measure expression ([total duration] / [conversion rate]). And it's at this point I'm stuck. Because there isn't a bridge between the conversion rate table and my fact table (ie. the exchange rate date like in Adv.Wrks), I can't define the relationship in the dimension usage tab. As well, the lack of a time dimension is preventing me from using any of the semi-additive measures adventure works uses (AverageOfChildren, LastNonEmpty) on the conversion rate measure.

Anyone know how I can get this to work? I've been searching and trying things, and can't seem to come up with a solution for what I thought would be a common problem.

I didn't have the enough reputation to be able to upload my images to the site. I hope the descriptions could help. If you want to see the complete document to this problem you can send me an email and I will send you back the complete doc with images. My Email Address is:
alireza.marahemi@gmail.com

Suppose you have the following data as measurement unit

Uom     ConvertRate( to smallest unit)
Second  1
Minute  60
Hour    3600
Day     3600* 24

Your fact table contains the data based on your smallest unit of measurement (in this case seconds). you want to be able to select other measurement units (e.g Hour) and your measure (Time in our example) displayed according to your selection. For instance if sum of your data items is 1200(sec) if you select Minute the result should be 20.
In this case I assume you do not want to add extra measures for each unit of measurement to your data.
Here is the design method.
First you have to create your Measurement unit table in following manner:

CREATE TABLE [DimUom]
(
    [UomID] INT NOT NULL primary key,
    [UomName] VARCHAR(50) NOT NULL,
    ParentUomID INT,
    ConvertRate float
)

I have used the fields ParentUomID and ConvertRate to identify the base measure and ConverRate of current measure to base measure.
you can fill this table with the following script.

INSERT INTO [DimUom] ([UomID], [UomName], [ParentUomID],[ConvertRate])
SELECT [UomID], [UomName], [ParentUomID],[ConvertRate]
FROM (
    SELECT 1 AS [UomID], 'TimeMaster' AS [UomName] , NULL as [ParentUomID], 1 as [ConvertRate]
    UNION ALL
    SELECT 2 AS [UomID], 'Seconds' AS [UomName], 1, 1
    UNION ALL
    SELECT 3 , 'Minutes', 1, 60
    UNION ALL
    SELECT 4 , 'Hours' , 1, 3600
    UNION ALL
    SELECT 5 , 'Days' , 1, 86400
    UNION ALL
    SELECT 6 , 'Weeks' , 1, 86400 * 7
    UNION ALL
    SELECT 7 , 'Months (30 days)' , 1, 86400 * 7 * 30
) A;

Your fact data can be as simple as the following fact table with only 3 fileds DataID as Primary key( not needed actually) your DataItem and your measurement unit.

CREATE TABLE [FactData] (
[DataID] INT primary key identity,
dataItem INT NOT NULL,
[UomID] INT NOT NULL);
GO

The following script adds a minimal data to your table. You have noticed that I used only the TimeMaster measurement unit to the table( Actually I could have used
only seconds). Anyhow your table should contain the smallest measurement unit.

INSERT INTO [FactData]([dataItem],[UomID] )
SELECT [dataItem],[UomID]
FROM (
    SELECT 600 AS [dataItem], 1 AS UomID
    UNION ALL
    SELECT 3600 , 1 AS UomID
    UNION ALL
    SELECT 86400 , 1 AS UomID
    UNION ALL
    SELECT 60 , 1 AS UomID
) A;

Now it is time to go through SSAS and build our dimensional model. In your data source view add your fact and dimension table.

You see after adding the tables I've changed the dimUom to a Named Query and added a field named AlternateParentID as following:

SELECT        UomID, UomName, ParentUomID, ConvertRate, ParentUomID AS AlternateParentID
FROM            dbo.DimUom;

I've done this because I want to relate FactData to DimUom via this field. I couldn't relate dimUom to FactData by ParentUomID field as I want to set ParentUomId usage as Parent in ssas and you can not relate a fact table to a dimension by a parent field.
After adding tables and editing the named query add the following relations to data source view.
FactData(UomID)  dimUom(AlternateParentID)
dimUom(ParentUomID)  dimUom(UomID)
After building the cube in the dimension usage tab of the solution you will see this structure:

After creating the cube go to dimUom attribute tab and set Namecolumn attribute of UomId to UomName field to display measurement unit name field instead of Id field.

Now we have to add the convert rate as a measure to the cube. So in the cube structure tab of the solution and select the "dimUom" as source table and ConverRate as the source column.

After adding the new measure your cube should look like this

and the structure as follows

After doing above instructions you have to create a calculated measure in the calculation tab as follows:

As you may have noticed in format string section I used a script instead of predefined format string. You can do this if script view of calculation tab and editing the calculation formula in following way.

CREATE MEMBER CURRENTCUBE.[Measures].[ConvertedRateQty]
 AS [Measures].[Data Item]/[Measures].[Convert Rate],
FORMAT_STRING = case [Dim Uom].[Parent Uom ID].Currentmember
WHEN
    [Dim Uom].[Parent Uom ID].&[2] THEN "#,#"
ELSE
    "#,#.0000"
END,

It is time to process the cube and view the results. Process the cube and go to the browser tab and add DataItem and ConvertedRateQty measures to the measure section. You will see the following results.

As we did not indicate any measurement unit the ConvertedRateQty shows our measure ( dataItem = 90660) divided by sum of all conversion rate of uomTable ( 1 + 60 + 3600 + 86400 + 7 * 86400 + 7 * 30 * 86400 = 18,838,861)
In our example 90,660 / 18,838,861 = .0048
If you select any individual measurement unit from you will see the converted DataItem.

Or you can see all the quantity in all measurement units converted simultaneously as follows:


Why is Convert being used in this statement and how does it differ from CAST? This is a piece of code on MSDN page for OVER clause:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                        ORDER BY DATEPART(yy,ModifiedDate)
                                       ),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                        ORDER BY DATEPART(yy,ModifiedDate)
                                        ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

So I am having issues understanding why CONVERT function had to be used there. I assume it is to do with return types of one of the fields in the expression part of the convert function? Can CAST be used instead?
Second question I have is, how exactly does this part work?

SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)

What exactly is that saying? Is it calculating sum for every year? Is that it?

Convert style

If SalesYTD type is float or real:

1 = Always 8 digits. Always use in scientific notation.
If type is money and small money:

1 = Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

  Cast will use the default value for style: 0

If SalesYTD type is float or real:

0 (default) = A maximum of 6 digits. Use in scientific notation, when appropriate.
If type is money and small money:

0 (default) = No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

There are other differences and default values for style with other types but I think it covers your data types. See CAST and CONVERT (Transact-SQL)

  SUM window function

For each partition (=TerritoryID) it takes all SalesYTD, order them by DATEPART(yy,ModifiedDate) and sum them. Note that the Order part is not necessary or could be anything since you only do SUM and AVG but using it change the behaviors (see hypercube answer about it and moving average). With ROW_NUMBER and others, it would be important and mandatory.

Diffence between SUM and moving SUM (@hypercube answer):

declare @data table(TerritoryID int, SalesYTD int, ModifiedDate date)
insert into @data(TerritoryID, SalesYTD, ModifiedDate) values
(1, 1, '20100101')
, (1, 2, '20110101')
, (1, 3, '20120101')
, (1, 4, '20130101')
, (1, 5, '20140101')
, (2, 6, '20150101')
, (2, 7, '20160101')
, (2, 8, '20170101')

Select *
    , [Moving SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate))
    , [SUM] = SUM(SalesYTD) OVER (PARTITION BY TerritoryID)
From @data

Output:

TerritoryID | SalesYTD | ModifiedDate | Moving SUM    | SUM
---------------------------------------------------------------------
1           | 1        | 2010-01-01   | 1 => 1        | 10 => 1+2+3+4
1           | 2        | 2011-01-01   | 3 => 1+2      | 10 => 1+2+3+4
1           | 3        | 2012-01-01   | 6 => 1+2+3    | 10 => 1+2+3+4
1           | 4        | 2013-01-01   | 10 => 1+2+3+4 | 10 => 1+2+3+4
2           | 6        | 2015-01-01   | 6 => 6        | 21 => 6+7+8
2           | 7        | 2016-01-01   | 13 => 6+7     | 21 => 6+7+8
2           | 8        | 2017-01-01   | 21 => 6+7+8   | 21 => 6+7+8

See OVER Clause (Transact-SQL) for window functions.


How to change the owner of a Stored Procedure in DB2? Let's say that I have a procedure called schema.proc1.
How do I change the owner of it? I'm using db2 express-c 10.5

I've tried the following command running as the olduser,

transfer ownership of procedure schema.proc1 to user newuser preserve privileges;

I want to do this because I believe this is a constraint that I'm having to not being able to create or replace schema.proc1 as newuser.

I would like to point out what I've done regarding privileges and the newUser (all of this were done with the oldUser).

TRANSFER OWNERSHIP OF SCHEMA mySchema TO USER newUser PRESERVE PRIVILEGES;
GRANT ALTERIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT CREATEIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;
GRANT DROPIN ON SCHEMA mySchema TO USER newUser WITH GRANT OPTION;

GRANT BINDADD ON DATABASE TO USER newUser with grant option;
GRANT CONNECT ON DATABASE TO USER newUser with grant option;
GRANT CREATETAB ON DATABASE TO USER newUser with grant option;
GRANT CREATE_EXTERNAL_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER newUser with grant option;
GRANT IMPLICIT_SCHEMA ON DATABASE TO USER newUser with grant option;
GRANT LOAD ON DATABASE TO USER newUser with grant option;
GRANT QUIESCE_CONNECT ON DATABASE TO USER newUser with grant option;
GRANT EXPLAIN ON DATABASE TO USER newUser with grant option;
GRANT SQLADM ON DATABASE TO USER newUser with grant option;
GRANT WLMADM ON DATABASE TO USER newUser with grant option;

grant createin on schema mySchema to newUser with grant option;
grant alterin on schema mySchema to newUser with grant option;
grant dropin on schema mySchema to newUser with grant option;

Event with all of this, still no luck on replacing a stored procedure.

Thanks in advance!

I believe you are correct on the syntax. A few points to note.

You cannot grant to yourself or revoke from yourself, so someone else may need to grant you the privileges you are after.
You may need the DB level privileges CREATE_EXTERNAL_ROUTINE and/or CREATE_NOT_FENCED_ROUTINE granted to you. May may also need BINDADD granted to you as well. These privileges allow you to create stored procedures.
You do need to be the owner of the stored procedure to do a REPLACE. Otherwise as long as you have CREATIN and DROPIN on the schema, you might be able to DROP and then CREATE the stored procedure.


Creating domain user login from another domain? I have two domains named "Domain1" and "Domain2". I have SQL server 2005 in Domain2, and users in Domain1.

How can I create a login in SQL Server 2005 (in Domain2) for the user that is present in Domain1?

In order for one domain to have access to objects in another domain, a domain trust needs to be established to allow it. (I'm assuming you don't have a parent-child domain relationship right now.)

This is a configuration change in Active Directory; SQL Server is not involved.

Once a trust is established, the server will be able to see the necessary domain objects when you create the login.


Join two sql's to return articles and its likes I am new to Oracle and need help with the following: I am having a sql which returns a list of articles-

SELECT LEADERSHIP.LDRSHP_ID
    , LEADERSHIP.LDRSHP_TITL_DESC
    , LEADERSHIP.LDRSHP_CNTNT_TXT
    , LEADERSHIP.LDRSHP_PUBD_DT
    , LEADERSHIP.LDRSHP_ATHR_NM
    , LEADERSHIP.MEDIA_URL
    , LEADERSHIP.ARTCL_SRC_ID
    , MEDIA.MEDIA_TYPE_DESCRIP
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP
INNER JOIN RSKLMOBILEB2E.MEDIA_TYPE MEDIA
    ON (LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF)
WHERE (LEADERSHIP.LDRSHP_ATHR_NM
    , LEADERSHIP.LDRSHP_PUBD_DT)
IN (SELECT LDRSHP_ATHR_NM
      , MAX(LDRSHP_PUBD_DT)
    FROM RSKLMOBILEB2E.LEADERSHIP_CORNER
    GROUP BY LDRSHP_ATHR_NM)
ORDER BY LEADERSHIP.LDRSHP_PUBD_DT DESC

I have to implement total likes for each articles which is returned by this sql

SELECT LEADERSHIP1.LDRSHP_ID
, COUNT(*) TOTAL_LIKES
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP1
, RSKLMOBILEB2E.LEADERS_LIKES LIKES1
WHERE LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID
group by LEADERSHIP1.LDRSHP_I

Now I need a single sql by combining above two sql's to return all the articles along with the total likes for each article. I will really appreciate any help on this.

You can simply join them. You have to use a left outer join. Otherwise the articles that didn't get any "like" yet, wouldnt be displayed.

SELECT A.LDRSHP_ID
    , A.LDRSHP_TITL_DESC
    , A.LDRSHP_CNTNT_TXT
    , A.LDRSHP_PUBD_DT
    , A.LDRSHP_ATHR_NM
    , A.MEDIA_URL
    , A.ARTCL_SRC_ID
    , A.MEDIA_TYPE_DESCRIP
    , B.TOTAL_LIKES
FROM
    (
            SELECT LEADERSHIP.LDRSHP_ID
                   , LEADERSHIP.LDRSHP_TITL_DESC
                  , LEADERSHIP.LDRSHP_CNTNT_TXT
                  , LEADERSHIP.LDRSHP_PUBD_DT
                  , LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.MEDIA_URL
                  , LEADERSHIP.ARTCL_SRC_ID
                  , MEDIA.MEDIA_TYPE_DESCRIP
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP
              INNER JOIN RSKLMOBILEB2E.MEDIA_TYPE MEDIA
                  ON (LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF)
              WHERE (LEADERSHIP.LDRSHP_ATHR_NM
                  , LEADERSHIP.LDRSHP_PUBD_DT)
              IN (SELECT LDRSHP_ATHR_NM
                    , MAX(LDRSHP_PUBD_DT)
                  FROM RSKLMOBILEB2E.LEADERSHIP_CORNER
                  GROUP BY LDRSHP_ATHR_NM)
      ) A
      LEFT OUTER JOIN
      (
              SELECT LEADERSHIP1.LDRSHP_ID
              , COUNT(*) TOTAL_LIKES
              FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP1
              , RSKLMOBILEB2E.LEADERS_LIKES LIKES1
              WHERE LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID
              group by LEADERSHIP1.LDRSHP_I
      ) B
              ON (A.LDRSHP_ID=B.LDRSHP_ID)
      ORDER BY A.LDRSHP_PUBD_DT DESC


query_cache_type = 0 vs query_cache_type = 1 Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf.

However, I  can see that, by default, the generated settings for my.cnf use query_cache_type = 0. (query cache is disabled).

The only thing I run on the server is a Wordpress blog. My questions are:

May I enable query cache?
There are some Wordpress plugins that offer database cache. Is the result similar of enabling query cache?

For a Wordpress blog it should be fine to set query_cache_type = 1. See, the major problems with the query cache are:

It invalidates very easily (any update on some table invalidates all queries related to said table)
It has a single mutex on which any incoming or outgoing query must go through.

The query cache was fine in the days where machines had one core, maybe two; it does not fare well with multiple cores and high concurrency, and it does not fare well with write intensive applications.

Your Wordpress blog is not likely to be write intensive: your writes are new posts, updates, comments... How many of these? You may not even counter them on a per-minute basis...

It would actually not be read intensive either. Hundreds of reads per day Thousands? That's nothing.

With regard plugins, that greatly depends on the plugin and on your deployment. Wordpress is written in PHP, and PHP might use such cache mechanisms as file system, memcached or APC. It should be typically "better" caching for the specific purpose it is oriented. That is, it will invalidate cached data based on real changes to relevant data -- not for irrelevant operations on some table. The programmers of the plugin would need to decide which pieces of data are important to cache, which are not, how invalidation works.

Again, in the scale of a blog, this is no big deal, and should not make a significant difference (of course there could always be some crazy plugin).

Bottom line: for your needs, anything goes and it does not matter much.


Run SQL Server 2016 side-by-side with older versions? Brent Ozar had a post SQL Server 2016 and the Internet: Forced Updates, Phoning Home.  "Issue #1" is ‘You probably shouldn’t run 2016 side-by-side with older versions.’  So, I checked the language in all the SQL Server install ULAs and they all say the same thing.  I know some shared components will be upgraded to the newest version on a server upon install of said version and feature, but this has been around for many Glenn Berry guided instance installs.  Is there something I’m missing?

At the risk of speaking directly for @BrentOzar - I don't think he's saying you can't install SQL Server 2016 alongside a prior version.  His blog post1 states:

You probably shouldn’t run 2016 side-by-side with older versions because:
... You may get updates whether you want them or not.

This does not say you will get updates that will bork your box.  Essentially, any Enterprise installation of SQL Server should be carefully managed - you probably shouldn't allow automatic updates of SQL Server at all, and I'd be quite afraid of letting Windows Update install any updates automatically without some kind of pro-active management.
Test your planned deployment on a non-production box to see if the installation of SQL Server 2016 will cause problems for your environment.  Then make an informed decision about how to go about applying updates.  Decide for yourself if you can afford the risk of a side-by-side installation, versus the extra cost of running multiple SQL Server boxes.


Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time? I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage of the database to 85-90% for hours. Then all of a sudden it goes back down to the normal 10% usage.

Queries against the server from the application still seem to be operating quickly during this overloaded state.

I can scale the server from S2 => anything (S3 for example) => S2 and it seems to clear out whatever state it is hung in. But then a few hours later it will again repeat the same overloaded state cycle. Another weird thing I've noticed is that if I run this server on an S3 plan (100 DTU) 24/7 I have not observed this behavior. It only seems to occur when I have downscaled the database to an S2 plan (50 DTU). On the S3 plan I am always sitting at 5-10% DTU usage. Obviously underutilized.

I've checked into Azure SQL query reports looking for rogue queries, but I don't really see anything unusual and it shows my queries using resources as I would expect.

As we can see here though, the usage is all coming from Data IO. If I change the performance report here to show top Data IO queries by MAX, we see this:

Looking at these long running quires seems to point to statistics updates. Not really anything running from my application. For example, query 16302 there shows:

SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.250395e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

But then again, the report also shows that these queries are only using a small percentage of the Data IO usage on the server (< 4%). I also run statistics updates (and index rebuilds) on the entire database on a weekly basis as part of its regular maintenance.

Here is another report that shows MAX data IO queries for a timespan that covers several hours only during the high-resource-usage incident.

As we can see, not really any queries reporting significant data IO usage.

I have also ran sp_who2 and sp_whoisacive on the database and do not really see anything jumping out at me (though I will admit I am not an expert with these tools).

How do I figure out what is going on here? I don't think any of my application queries are to blame for this resource usage and I get the feeling that there is some internal process running in the background on the server that is killing it.

Given that during the spike(s) your log activity is minimal we can assume there isn't any (or much) DUI going on.

You mention at one point that the spike doesn't affect performance, and at another that it does. Which is it?

You also mention that this goes away after a scale operation. This makes sense as it's analogous to an on-premise restart which will effectively kill all processes etc.

Do I assume correctly in guessing that this database is being accessed from the application-tier? If so, I suspect that your connections are not being closed properly. The garbage collector is supposed to take care of these eventually (which shouldn't be relied upon), but I've seen this exact situation occur because of unclosed connections from the app-tier. In our case, the application was so busy that we eventually received concurrent connection errors which is what lead us to the problem.

Try the following query during the spike:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    s.status,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
ORDER BY c.connect_time ASC

If I'm correct, you'll find a whole bunch of records returned with the status of Sleeping, or worse Running. If that's the case you have even bigger problems in the app-tier.

We can further debug this by copying the database, using the following query (using basic tier to avoid excessive costs), and monitoring for this behaviour.

CREATE DATABASE Database1_copy AS COPY OF Database1 ( EDITION = 'basic' );