OLAP window functions in DB2 I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query

declare global temporary table T (reference integer, val integer) on commit preserve rows;
insert into session.T values(1, 10);
insert into session.T values(2, 20);
select first_value(val) over (order by reference) from session.T;

I am given the error

NO AUTHORIZED FIRST_VALUE NAMED  HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.18.60. 2) [Error Code: -514, SQL State: 26501]  THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE.. I am aware of the listing of error codes. However, these descriptions do not help me to understand the problem.

You are mixing up Db2 (Db2 for Linux, Unix and Windows) and Db2 for z/OS.

Db2 9.7 is LUW (Linux, Unix and Windows) and FIST_VALUE is available on these OS'es.

Db2 for z/OS does not support FIRST_VALUE.


DB2 - Not enough storage in the database heap error when trying to create database So like the title says I'm trying to create a database using this script

db2 -v -t "CREATE DATABASE %DBNAME% ON '%DBDRIVE%'
USING CODESET UTF-8 TERRITORY US COLLATE
USING SYSTEM CATALOG TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\sys') TEMPORARY TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\systmp') USER TABLESPACE MANAGED BY SYSTEM
USING ('%DBDRIVE%%DBDIR%\%DBNAME%\usr')";

but I get an error that says:

  SQL0956C Not enough storage is available in the database heap to process the statement. SQLSTATE=57011

I've looked online about this error and it says to use this command to increase the database heap

db2 UPDATE DB CFG FOR <db-name>
  USING DBHEAP <value>

I've set every database's dbheap value to automatic. I've also checked the values in the dbm such as: database monitor heap size (4KB) -> AUTOMATIC(66) and Size of instance shared memory (4KB) -> AUTOMATIC(1000000). So everything is set to automatic but there is still "not enough memory". also I looked in the db2diag.log file and this seems to be the main error:

2014-02-12-09.09.38.562000-480 E36299119H2188       LEVEL: Error (OS)
PID     : 2540                 TID : 7140           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000           DB   : COM1_513
APPHDL  : 0-11886              APPID: *LOCAL.DB2.140212164245
AUTHID  : ADMINISTRATOR        HOSTNAME: CM-RM-WIN04
EDUID   : 7140                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
CALLED  : OS, -, VirtualAlloc
OSERR   : 8 "Not enough storage is available to process this command."
MESSAGE : Private memory and/or virtual address space exhausted
DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
1048576
DATA #2 : Current set size, PD_TYPE_SET_SIZE, 4 bytes
65536

This seems to be saying there is not enough memory on the machine, which is weird because when I see the PF Usage in Windows Task Manager only about 3 / 12 GB of RAM is in use...

I am using DB2 v10.1 and Windows 2003 Server 32 bit

Also I believe this problem is due to the memory management of the databases I've made on this machine. Not positive though.

On a 32-bit Windoes system each process, including the DB2 instance, is limited to 2 GB of memory space (3 GB at most, with the appropriate startup switch), regardless of the amount of physical memory available. Your instance configuration requests 4 GB (Size of instance shared memory (4KB) -> AUTOMATIC(1000000)), which cannot be satisfied.

Try changing the INSTANCE_MEMORY parameter to a smaller value, e.g. 50000 pages.

By the way, with DB2 10.1 you should be using automatic storage instead of specifying tablespace containers explicitly.


SQL Rebuild Indexes Handling I have an instance which having 25 large databases.

I have set a rebuild_all_indexes job to rebuild all indexes on every database.

Due to database large size and OLTP environment this job takes about more than 3,4 days to its completion.

Can any one help me out to handle this situation?

As job step I used some store procedures calling like this:

exec database1.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database2.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database3.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database4.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database5.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database6.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database7.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database8.dbo.SP_JOBS_REBUILD_DB_INDEXES
go

...

exec database25.dbo.sp_jobs_rebuild_db_indexes
go

Try using Ola Halengren's SQL Server Maintenance Solution.

It has a lot of options, most important thing - it skips indexes that doesn't need maintenance (depending how You configure it).

There are few thing about index maintenance to remember. From my experience 3-4 days for full rebuilds on bigger databases isn't that much, depending on Your storage.

In some cases rebuild isn't needed, index reorganize is often enough for fragmentation between 5% and 30% (as stated in Microsoft recommendations in Books Online, and any kind of index maintenance is recommended only for indexes bigger than 1000 pages, while difference in performance might be noticable on indexes bigger than 50000 pages (as stated here).

In most cases index defragmentation isn't source of performance problems either, it just masks other issues hidden beneath. Defragmenting them too often won't improve Your performance and will stretch Your maintenance windows.

Another thing is, that maintenance creates noticeable load on IO and CPU, rebuild/reorganize operations are written in transaction log (for databases in FULL RECOVERY mode). So Your log files will get bigger, same with log backups.

More read about index maintenance on Brent Ozar's blog posts:

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

https://www.brentozar.com/archive/2014/04/index-fragmentation-bad-statistics-arent-always-problem-video/


Removing the Oracle Instance details from windows machine Actually, I have installed the Oracle 11g Version 1 in my machine. After that instead of
using universal installer for uninstallation manually I had removed the files from installed location.

Now, the impact is if I install the same version in my system, I get the error like the SID
is already available in your system.

What are the ways to remove that installed instance details?

Please help me to get know about that.

First, the pro-forma:  Better to uninstall the application rather than just delete the files.  Yes, it doesn't help, but it is true and someone will bring it up, so saving time...

Now, assuming that you just want the SID to be able to go through.  Normally, the SID is stored in the registry HKEY_LOCAL_MACHINE\SOFTWARE\Oracle.  It can be in several places there.  Please note that several pieces of Oracle software use this key (e.g. Oracle client) so be careful; probably worth backing up with a registry export command first.

You would also need to check the Windows Services (from a command prompt, type "services.msc").  The Oracle services begin with "Oracle" in their name, so they are easy to find.  If you see some with the SID in the name, you probably need to remove them before installing.  I would use SC.exe to install this as explained here (https://stackoverflow.com/questions/330836/how-to-uninstall-a-windows-service-when-there-is-no-executable-for-it-left-on-th).

If you want a more complete purge, you are in for tons of registry editing, as Oracle often leaves other stuff in the registry.

Good luck, and I hope that this helps,


Intermittent EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb' I have a job that runs every 2 minutes and sends an email to users after it has gathered job information. Most of the time it is successful but every couple of days it fails with the Execute permission was denied. Right after the failure it runs again just fine.

It is a job that goes through tables and gets information and status of certain jobs. Not SQL Server jobs but proprietary application jobs that has its information stored in tables in the databases. It fashions an email and sends it in HTML format from msdb.dbo.sp_send_dbmail.

It is owned by sa and is not run manually. I'm thinking that since it only happens once every couple of days (sometimes 3 or 4), it could be the content of the message is too big or has some garbage in it that prevents the msdb.dbo.sp_send_dbmail proc from sending.

There is no error handling on the job or step. There is only one step: It executes the proc that aggregates the information and sends the email. The agent log says the same thing that's in the title of the question about permissions.

SQL Server is setup to log failed logins. There have been no failed logins as of a week ago.

Resolved this by setting up a maintenance plan to clean up MSDB. It was very full! Waited to post the answer because I wanted to make sure we went through a few weeks without getting that Execute permission denied error.


Postgres multidisk master replicating to single disk slave I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, time-insensitive queries from the master.

Because time is not a concern on the slave, I have set up a machine with only one disk. However, when I try to set up replication, psql tries to mimic the structure on the master.

$ /usr/pgsql-9.2/bin/pg_basebackup -h [master] -D "/opt/database" -U [user]
pg_basebackup: could not create directory "/opt/archive-tables": permission denied

Because I have only one disk, I need all data to be stored in /opt/database (where the disk is mounted).

Is there some way to override the master tablespace structure and force everything to go into a single tablespace (or at least onto a single file location)?

Community Wiki answer generated from a question comment by a_horse_with_no_name

Create symbolic links on the slave that match the mount points of the tablespaces on the master.


Cassandra backup from all nodes or just one? Cassandra is a distributed database, where each node is in sync with the other nodes from the same ring/cluster.

When taking backups based on a snapshot, do I need to back up each node individually or is one enough?

The docs say:

  To take a global snapshot, run the nodetool snapshot command using a
  parallel ssh utility, such as pssh.

Am I missing a point here?

the short answer -> you have to take snapshots on all nodes.

As you pointed out, Cassandra is a distributed database. As an example, suppose you have 3 nodes with a replication factor (RF) of 2. Each node has primary responsibility for 1/3 of all the tokens in the ring. In addition, each node has a replica from the another node, and nodetool status will show "Owns 66.6%" (2 replicas / 3 nodes).

If you only backup one node, you only get the data on that node plus whatever replicas are being stored on that node. Since the data is distributed, you will end up missing some data unless you take snapshot on all nodes.


Connection string for connecting mongo database with SSL enabled Am facing some below problem in my mongodb replication environment

I have 3 replica set environment with SSL enabled and ssl mode as preferSSL

3 servers has 3 different Key file with common CA file

when i try to connect the mongo shell with below command it works fine

mongo --ssl --host hostname:port --sslPEMKeyFile "/home/mongod/keyfile.pem" --sslCAFile /home/mongod/CAfile.pem

My question is how to provide these details in connection string to connect the database from application perspective

MongoDB version: 4.0.2
OS: Amazon Linux

Thanks,
Vignesh

It depends on which driver you are using.
https://docs.atlas.mongodb.com/driver-connection/#driver-examples


AWS RDS same file_guids Getting this error on AWS RDS with MS SQL

  Database XXX cannot be restored because there is already an existing
  database with the same file_guids on the instance.

I've imported from S3 once, it worked. Deleted the database and tried to import a more recent version FULL DB and it broke.

I've created a new instance also but still get the error. I can import other DBs from S3 fine.

Anyone have any ideas that can help?


user "sa" cannot connect to SQL Server Express 2008 R2 Goal: connect to SQL Server Express 2008 database using SQL Server authenticated account "sa", rather than my Windows authenticated account.

When installing SQL Server 2008 Express, I took all default options except when filling in the Service Accounts.  It did not allow me to enter "sa" as the Account Name for the "SQL Server Database Engine" service.  So I found my network user {NETBIOS DOMAIN\LOGIN} and entered that id along with my network password.  I then chose the default "Startup Type" of Automatic.  That allowed me to complete the installation.

Normally, on Windows XP or Windows 7, SQL Server Express installs fine.  This install was on Windows Server 2008.

When I created a new database, I noticed my network name showed up under the database properties.  Database Properties > General > Owner.  So I reset that user to "sa" for that one database.  Creating new databases defaults it to the value I initially set during install.

I used this command to reset that user.

EXEC sp_changedbowner 'sa'

The latest version of SQL Server Express (downloaded today) also defaulted the "sa" login to  Disabled (Database Server > Security > Logins > sa > Login Properties > Status > Login: Disabled).  So I enabled it.

http://sarangasl.blogspot.com/2011/06/sql-server-authentication-enabling.html

I still cannot log into the database server ({MACHINE NAME}\SQLEXPRESS) with account sa.  I event reset the password (Database Server > Security > Logins > sa > Login Properties > General > Password & Confirm Password).  What am I missing?

I had to do two things to solve it.

1.) I didn't have mixed mode turned on.  I had to right click the server node in the Object Explorer and go to Server Properties.

https://stackoverflow.com/questions/2474839/unable-to-login-to-sql-server-sql-server-authentication-error-18456

2.) restarted Windows Service for SQL Server.


MySQL replication works but slave keeps reconnecting to master I have a master-slave set-up with multiple slaves in different datacenters. One of the slaves stopped working normally today. This was easily resolved by restarting mysqld but ever since (and perhaps before) the Slave_IO_State keeps jumping from "Waiting for master to send event" to "Checking master version" to "Reconnecting after a failed master event read" (the order may be different, but these are the statuses I get from continuously running a SHOW SLAVE STATUS query).

The connection to the master is made via a SSH tunnel, and I'm able to connect to the master from the command line without a problem. Furthermore, replication seems to be working; the slave is up to date. What I don't understand is why it keeps disconnecting/reconnecting. No other errors show.

This is MySQL 5.5.54 running on CentOS 6 boxes.

Resetting the slave doesn't help. How do I get to the root of this issue?

EDIT: When it really fails, as before and it just happened again, the message is "The slave I/O thread stops because SET @master_heartbeat_period on master failed. Error: Lost connection to MySQL server during query".

It turns out my slave's server_id was too long. I was naming these after the slaves' public IP addresses, and this IP was longer than the max value of 4294967295. I should have checked when I decided to use this naming scheme, but a warning from MySQL would also have been nice.

All's well that ends well. I now use the last three decimals of the public IP address (9 digit max).


Defining a GIN index across two columns/arrays with similar data Let's say we have a Postgres table contacts, each record having a bunch of labeled email addresses (pairs of labels and emails)-- one of which is the "primary".

This is stored like:

id primary key
email text
email_label text
metadata jsonb

emails array
email text
label text

For example, a record might look something like:

{
  id: 1,
  email: 'a@a.com',
  email_label: 'a',
  metadata: {
    emails: [
      {
        email: 'b@b.com',
        label: 'b'
      },
      {
        email: 'c@c.com',
        label: 'c'
      }
    ]
  }
}

Given this storage pattern, we want to be able to find a record by any of its email addresses.

The naive query would look like:

SELECT id
FROM contacts
WHERE
  email = 'my@email.com' OR
  metadata -> 'emails' @> '[{"email": "my@email.com"}]'

Is there any way to create an index that speeds up this operation significantly? It would need to update automatically in response to changes to the records, and ideally index across both the text column and the nested JSONB column.

The specific use case here would be able to do lookups by email address efficiently and quickly, without overhauling this structure or creating a new relational table.

I believe the solution involves using a GIN index and this question mentions jsonb_path_ops but I'm not sure how to wrangle all the pieces together.

Since you want to run the query on metadata->'emails', you need to build the index on that expression:

create index on contacts using gin ((metadata->'emails'));

Alternatively, you could make the index on metadata itself, and re-formulate the query condition like this:

... OR metadata @> '{"emails":[{"email": "my@email.com"}]}'

In either case, you also need an index on the column "email", to satisfy the other branch of the OR condition.  Then the two indexes will be combined with a BitmapOr operation.

It is possible you could make one index on both columns using some complicated expression index, but that doesn't really seem worthwhile.


INTERSECT query rewrite as JOIN? I have two queries written in PHP against a Microsoft SQL Server database (I know this is weird).

I'm querying one table.

I want the records shared by both results sets ONLY.

Here is query #1:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'DONE')

Here is query #2:

SELECT     ORDER_NO
FROM         X_INVOIC
WHERE     (ITEM_CODE = 'LAPMACPRO15.4')

The results of both queries have some of the same ORDER_NOs between them.

Example Results Set #1:

101
102
103

Example Results Set #2:

105
102
106

My query should only return "102", since it is common in both results sets.

I simply want to compare the results and show a list of ORDER_NOs that are in both results sets. An INTERSECT query would work perfectly for this, but I get this error message when trying to test my query in the SQL Server Manager:

The INTERSECT SQL construct or statement is not supported.

Is there a way to do this with INNER JOINS?

I have tried the following already, which involved joining the table to itself as an alias, but it returned no results.

select a.ORDER_NO,
       b.ORDER_NO
  from X_INVOIC a
  join X_INVOIC b on b.INVOICES_ID = a.INVOICES_ID
where a.ITEM_CODE =  'LAPMACPRO15.4'
  and b.ITEM_CODE = 'DONE'

Thanks.

Yes, it's possible, The query you already tried was on the right lines but had the wrong join condition (b.INVOICES_ID = a.INVOICES_ID should be b.ORDER_NO = a.ORDER_NO)

try this

SELECT DISTINCT a.order_no
FROM   x_invoic a
       INNER JOIN x_invoic b
         ON a.order_no = b.order_no
WHERE  a.item_code = 'DONE'
       AND b.item_code = 'LAPMACPRO15.4'


Load data into postgresql from file with trailing delimiter Context

I receive a number of data files that I'd like to load on a monthly basis into Postgresql (currently running 11.4). However, some of the files each month are formatted such that they have a trailing delimiter at the end of each line.

For example, if I have the following table:

CREATE TABLE t1 (
  id integer,
  make varchar(50),
  model varchar(50)
);

The corresponding file looks like:

1|Ford|Taurus|
2|Honda|Civic|
3|Toyota|Camry|

Note that while the | character is used as a delimiter, there is an "extra" one at the end of each line -- which causes a load to fail:

=> BEGIN TRANSACTION;
BEGIN
=> TRUNCATE TABLE t1;
TRUNCATE TABLE
=> \copy t1 (id, make, model) FROM t1.dat WITH FREEZE NULL '' DELIMITER '|';
ERROR:  extra data after last expected column
CONTEXT:  COPY t1, line 1: "1|Ford|Taurus|"
=> COMMIT;
ROLLBACK

Solutions I've thought of so far:

Load into a temp table that has an extra dummy column, then INSERT AS SELECT; however I believe that loses the speed/efficiency of COPY WITH FREEZE, plus you touch the data twice which I'm guessing is bad as the size of it grows.
Add a dummy column onto each table; don't know a priori which files/tables need this.
Ask the data provider not to do this; it comes from a branch of the US Government, so a very long shot...
Pre-process the files to remove any / a single trailing delimiters; again don't know a priori which files will need this and it could change month to month.

Anyone have any other ideas or recommendations on how to solve this?

You could also pre-process the file as suggested by Vérace:

  Or  sed s'/.$//'

from How to shave off last character using sed? by codaddict on Stack Overflow.

Or sed -i ... (inplace - I tend to avoid this, because in case of a mistake, you've lost your original file) or sed .... > table.txt.


SQL Server - storing audit to table Is there a standardised way to store auditing information constantly to a table for further analyse?

This really depends on what type of information you want to audit.  There are a number of ways to collect audit data.

I did an article that overviews audit options here.

But in summary:

Successful/Failed logins: This is an instance level setting and is saved to the logs.
C2 Auditing and Common Criteria Compliance: This goes a bit overboard in my opinion.  It audits a LOT of information.  You'll know if you actually need it.
Audit columns: These are columns in your table that give you information. CreateDate & ModifiedDate for example
Triggers: These are good if you want to log specific information when doing an insert, update or delete on a table.  There can be a lot of overhead here and they can be tricky to get right.
Tracing: Otherwise known as profiler.  This is an older technology that has been deprecated but is still hanging on and will for who knows how long.  You can collect all kinds of information here about what was run, by whome, how long did it take, who logged in, who logged out, just about anything happening on your instance.  Remember though that the more you collect the more overhead.
Extended Events: This is the replacement for traces.  Basically the same but with less overhead and more options for data you can collect.
Audits: These can be created at a database or server level.  They are based on Extended Events but have a different set of information.  For example shutting down the instance can be logged.  These are meant for when you need to collect audit information in the traditional sense. The data that is collected for an auditor.

You can get a bit more detail from the link above and it has further links in at least a few places for even more detail.


How to SUM values with same value by using WHERE IN() I'm trying to sum the total of items in a cart by ID, so i was trying simply to SUM the values by passing IDs in the WHERE clause.
But if i'm using the WHERE IN to add the IDs the SUM is going to sum only different IDs so if i have in WHERE ID 104 twice it will sum it only once.
My query looks like this:
SELECT SUM(IMPORTO) AS TOTAL
FROM (
    SELECT SUM(PRE_PRP) AS IMPORTO
    FROM vo_plurep
    WHERE ID_PLUREP IN (12782 , 12800, 12804, 12810, 12802, 12798, 12784, 12798, 12802)

    UNION ALL

    SELECT SUM(PRE_VAR) AS IMPORTO
    FROM vo_varianti
    WHERE ID_VARIANTI IN (104 , 104, 142)
) AS TOT

Is there a way to force MySql to sum even the same values in where clause?

The IN predicate will either evaluate to TRUE or FALSE (or NULL) exactly once for each row, so you won't get any multiplier effect from that. You can use a Common Table Expression (CTE, only supported in 8+) or a temporary table
CREATE TABLE vo_plurep
( ID_PLUREP int not null
, PRE_PRP int not null
);

insert into vo_plurep (id_plurep, pre_prp) values (1,1),(2,2);

CREATE TEMPORARY TABLE T (id_plurep int not null);

insert into T (id_plurep) values (1),(1);

SELECT SUM(PRE_PRP) AS IMPORTO
FROM vo_plurep
JOIN T
    ON T.id_plurep = vo_plurep.id_plurep

IMPORTO
2

Fiddle


How to format String with a pattern I want to format my string in my select statement but I cant seem to find the syntax for this pattern.

I have a column of card_num with 16 digits and i want to have a dash(-) after the first four digits.

Original card_num:
1234567891234567

Desired output:
1234-567891234567

I think regex can do this but cant find elsewhere the right code for this pattern.

Any idea how to do this? Thanks.

You could use substr() and concatenation (Oracle 12c): select the first 4 characters, then concatenate '-' and the rest of the string.

create table t
as
select '1234567891234567' card_num from dual;

select card_num
, substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4)
  formatted
from t;

SQL> select card_num
  2  , substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4)
  3    formatted
  4  from t;

CARD_NUM          FORMATTED
1234567891234567  1234-567891234567

... the table is "overkill" for this example - but it may make it easier to experiment/adjust your query.


ORA-00959: tablespace does not exist but I can see it in select So, I am connected as SYSDBA to Oracle and trying to run this command:

 create user C##demo identified by demopassword;

That ends with User created.

Then I run:

alter user C##demo default tablespace PAVEL_DATA temporary tablespace PAVEL_TEMP;

Which ends in error:

  ORA-00959: tablespace 'PAVEL_DATA' does not exist

But if I run this command:

SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM USER_TABLESPACES;

I can see the tablespaces:

  TABLESPACE_NAME          STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                 ONLINE    PERMANENT
SYSAUX                 ONLINE    PERMANENT
TEMP                   ONLINE    TEMPORARY
USERS                  ONLINE    PERMANENT
UNDOTBS2               ONLINE    UNDO
PAVEL_DATA             ONLINE    PERMANENT
PAVEL_TEMP             ONLINE    TEMPORARY

So, why am I getting the error tablespace does not exist when it is created?

EDIT

Continued according the comments, this is what I see when I run select tablespace_name, con_id from cdb_tablespaces :

TABLESPACE_NAME            CON_ID
------------------------------ ----------
PAVEL_DATA              1
PAVEL_TEMP              1
SYSTEM                  4
SYSAUX                  4
TEMP                    4
USERS                   4

TABLESPACE_NAME            CON_ID
------------------------------ ----------
SYSTEM                  3
SYSAUX                  3
TEMP                    3
USERS                   3
EXAMPLE                 3
PAVEL_TEMP              3
PAVEL_DATA              3

So, both tablespaces are created in DB$ROOT (id=1) and in pluggable datavase (id>2). But still, when connected to the pluggable database, I am getting the same error. It must be something stupid, but I am blind now...

While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:

  DEFAULT TABLESPACE
  TEMPORARY TABLESPACE
  QUOTA
  PROFILE

More

According to the output you have updated it appears that the PAVEL_DATA tablespace doesn't exist in container number 4.

Moreover, the CREATE USER or ALTER USER command with DEFAULT TABLESPACE or DEFAULT TEMPORARY TABLESPACE clause may succeed if the default/temporary tablespace specified don't exist on the PDBS which are currently closed. But you may get errors when you try to open or try to connect these newly opened PDBs using the common user you have just created or altered.

Demonstration:

SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;
create user c##_sales_hr identified by password default tablespace tbs_sales
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORAPDB1
ORA-00959: tablespace 'TBS_SALES' does not exist

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME                CON_ID
------------------------------ ----------
SYSTEM                         1
SYSAUX                         1
UNDOTBS1                       1
TEMP                           1
USERS                          1
TBS_SALES                      1
SYSTEM                         4
SYSAUX                         4
TEMP                           4
ORAPDB1_TBS1                   4
MGMT_ECM_DEPOT_TS              4

11 rows selected.

SQL> alter session set container=orapdb1;

Session altered.

SQL> create tablespace tbs_sales datafile '+DATA' size 50M;

Tablespace created.

SQL> conn / as sysdba
Connected.
SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;

User created.


Not exists/Where NOT in Doing a pie chart in crystal. Items are

Inquiries
Referrals
Followups

A referral has to have an inquiry, A followup has to have a referral.

I want to show pie chart of referrals to inquiries so the slice would be the referred count, the rest of the pie would be the non-referred inquires.

Table A:

customer001 INQ 1/1/2013
customer001 INQ 9/30/2013.
customer001 REF 10/15/2013.
customer001 FOL 10/30/2013.

This customer has 4 records. First off I will ignore the followup record in my crystal reports record filter.

That leaves:

customer001 INQ 1/1/2013
customer001 INQ 9/30/2013
customer001 REF 10/15/2013

Now when I run my chart the pie will show 2 INQ's and 1 REF in the pie chart.
But I want it to be a 50/50 pie, because the INQ record that is most recent belongs to the REF record of 10/15.

I need a SQL statement that removes the 9/30/2013 record out of a view.
I have written code (Top(1) MAX of the date field in a view that gives me this output with the most recent INQ record inserted to the end of all records. Now I need a second statement that strips the duplicate record out.

ID---------Code--Date----INQcode---INQdate
customer001 INQ 1/1/2013 INQ 9/30/2013
customer001 INQ 9/30/2013 INQ 9/30/2013
customer001 REF 10/15/2013 INQ 9/30/2013.

So in my second unwritten view I need to write out only the records where code<> INQcode AND Date<>INQdate

If you use the ROW_NUMBER function partitioning over "Code" and ordering by "Date" descending like the following query

SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
FROM   A
WHERE  CODE <> 'FOL'

you will get.

    RNO ID           CODE       FDATE
    1   customer001  INQ        09/30/2013
    2   customer001  INQ        01/01/2013
    1   customer001  REF        10/15/2013

Then if you use a left outer join using that same select statement on RNO where the code of the right table is 'REF' like below

SELECT a1.ID,
   a1.CODE AS Code1,
   a2.CODE AS Code2,
   a1.fdate AS fdate1,
   a2.fdate AS fdate2
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM A
  WHERE CODE <> 'FOL'
) AS a1
LEFT OUTER JOIN
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM   A
  WHERE  CODE <> 'FOL'
  ) AS a2
  ON a1.rno = a2.rno
  AND a2.CODE = 'REF'
  WHERE  a1.CODE <> 'REF'
  ORDER BY FDATE1

and finally use a where clause filter on the left table where code is not equal to 'REF' you will get the following.

    ID          CODE1   CODE2   FDATE1      FDATE2
    customer001 INQ     (null)  01/01/2013  (null)
    customer001 INQ     REF     09/30/2013  10/15/2013

Hope this helps or is what you are looking for.


CSV data export to SQL Database with double quote problems I am trying to import data from falt files (.CSV) into SQL table. I am able to import them no problem but the data is coming with double quotes. What is the best way of resolving this problem?

Regards,

Set the text delimiter in the flat file reader properties to the double quote.


Should Data and Indexes be split in separate file groups and files At what point should we introduce multiple datafiles/file groups? Right now the DB is about 2 TB and a very active ERP. We are seeing massive deadlocks and poor performance. I am wondering, at point point do I know if adding a second Data file or File group is the answer?

Improvement (or not) will depend on your storage system. Multiple files can help performance if it increases IOPS and bandwidth with additional HBAs and drives. Multiple filegroups allows more granular placement of database objects. That will generally help in specialized use cases, such as separating sequential and random IO patterns on spinning media.

That said, I don't expect storage tuning to address the symptoms of "massive deadlocks and poor performance". Query and index tuning is the first thing I suggest you look at.


Fetching recent/historical queries run on a database from an application I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error.

How can I check a history log of queries to pinpoint the problem? I'm using Oracle 12c

There is no such thing enabled by default in the database. V$ or DBA_HIST views are not guaranteed to contain all your SQL statements all the time. The amount of memory to store SQL statements in V$ views is limited, and DBA_HIST views contain only sampled data. Furthermore, they do not store information about constraint violation errors.

If you want to find the SQLs causing unique constraint violation errors, you can create a trigger that catches the errors and logs information, for example:

create table userLog
(error_date date,
 username varchar2(30),
 error_msg clob,
 error_sql clob);

create or replace trigger log_errors after servererror on database
declare
   sql_text ora_name_list_t;
   msg clob := null;
   stmt clob := null;
   errnum number;
begin

   errnum := ORA_SERVER_ERROR(1);

   if (errnum = 1) then
       for i in 1 .. ora_server_error_depth loop
          msg := msg||ora_server_error_msg(i);
       end loop;
       for i in 1..ora_sql_txt(sql_text) loop
          stmt := stmt||sql_text(i);
       end loop;
       insert into userLog(error_date,username,error_msg,error_sql)values (sysdate,ora_login_user,msg,stmt);
   end if;
end;
/

The above trigger catches number 1 errors (that is for ORA-00001: unique constraint () violated) on the top of the stack (that is ORA_SERVER_ERROR(1)) in the database, and logs them in the userlog table with the SQL text, so you can find later the SQL statements that caused them.


Postgresql - archive_cleanup_command with multiple standby servers Regarding the archive_cleanup_command command, the Postgresql 9.6 documentation states the following:

  Note however that if multiple standby servers are restoring from the
  same archive directory, you will need to ensure that you do not delete
  WAL files until they are no longer needed by any of the servers.

Is there a built-in way that the master can know when all of the slaves do not need particular WAL files?  Or does that functionality need to be added via scripting or other mechanism?  If so, what would that look like?

Also, would it be typical to have an archive_cleanup_command on both the master and the slaves, or would the slaves even be archiving?

The built-in way to do this is to use replication slots and to do away with a WAL archive altogether.  Replication slots will cause the WAL to be retained in pg_xlog until all standbys have received it, rather than stored in the WAL archive directory.  So make sure you have enough space available for this purpose, i.e. assign the space currently set aside for WAL archive to be added to the available space for pg_xlog.

An alternative is to use "wal_keep_segments" rather than replication slots, and define it at a little less than how much space you have reserved for pg_xlog.  Any standbys which fall behind by more than this amount will be sacrificed, rather than being allowed to bring down the master.

For your other question, archive_cleanup_command cannot run on the master (it is set in recovery.conf, which the master doesn't have), nor would it make any sense for it to do so.


Does mysqldump -all-databases include all objects Does mysqldump -all-databases include all objects ?

I have to migrate all databases to new server.

No mysqldump -all-databases does not include all objects

mysqldump --help

  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.

So mysqldump with --all-databases only dumps all the databases.

In order to migrate all the databases to a new server, you should take a complete backup :

mysqldump an entire mysql instance

mysqldump -h... -u... -p... --events --routines --triggers --all-databases > MySQLData.sql

The drawback is that backups created this way can only be reloaded into the same major release version of mysql that the mysqldump was generated with. In other words, a mysqldump --all-databases from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is totally different between major releases.

Here is the generic way to dump the SQL Grants for users that is readble and more portable

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Have a look at answer from RolandoMySQLDBA on How can I optimize a mysqldump of a large database ?


pgadmin: can't insert jsonb[] values with strings I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "two"}, {"three": "four"}}.

How to even insert nested string values like:

{ {"one", "two", "three"},
  {"four", "five", "six"},
  {"seven", "eight", "nine"} }

ERROR: invalid syntax..., token "one" invalid

The answer is: in pgadmin you have to escape every quotation mark before and after key/value definition.
What a sophisticated solution.

{
    "{\"one\": \"two\"}",
    "{\"three\": \"foure\"}",
    "{\"fivep\": \"six\"}"
}


Syntax error in simple trigger I have a SQLite table with these fields among others

Fld_QuoteNumberRoot     string
Fld_Revision            integer
Fld_QuoteNumber         string

I need to fill Fld_QuoteNumber on every UPDATE and on every INSERT like this:

'Q00897' + 4 => 'Q00897/4'

Using this trigger

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision)
END;

I always the error:

  near "END": syntax error

Questions
What is wrong with my syntax?

Can I create a trigger which fires on UPDATE and on INSERT or do I need to define two triggers?

The UPDATE Quote_Base ... needs a semicolon at the end ... Fld_Revision);

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision);
END


Always on basic question I've tried to find this on Google but no love. Usually that means it's so basic everyone else knows it. But I would like an answer for my own peace of mind.

If I make a change to one server in an Always On situation - like say adding an index - do I have to make the same change to the other server? And if I do, is there a risk of the two servers not being in sync?

Any links to articles would be fine. Just want to be certain.

TIA

If I make a change to one server in an Always On situation - like say adding an index - do I have to make the same change to the other server?

No, you just make the change to Primary (master replica) database which is writable. Rest other databases in an availability group are either read only or not accessible depending on if you have opened your secondary replicas for read or not.

  And if I do, is there a risk of the two servers not being in sync?

If the replica sync mode is set to Synchronous then it will always be in sync. If it is set to Async, then there it might lag a bit. In my experience, unless you have a very high latency, its always near to instantaneous for async replicas as well.

for few concepts, this is very detailed info that you will find on how it works.


Changing primary key from IDENTITY to being persisted Computed column using COALESCE In an attempt to decouple an application from our monolithic database, we've tried to change INT IDENTITY columns of various tables to be a PERSISTED computed column that uses COALESCE. Basically, we need the decoupled application an ability to still update the database for common data shared across many applications while still allowing existing applications to create data in these tables without the need for code or procedure modification.

So essentially, we've moved from a column definition of;

PkId INT IDENTITY(1,1) PRIMARY KEY

to;

PkId AS AS COALESCE(old_id, external_id, new_id) PERSISTED NOT NULL,
old_id INT NULL, -- Values here are from existing records of PkId before table change
external_id INT NULL,
new_id INT IDENTITY(2000000,1) NOT NULL

In all cases the PkId is also a PRIMARY KEY and in all but one case, it is CLUSTERED. All tables have the same foreign keys and indexes as before. In essence, the new format allows the PkId to be supplied by the decoupled application (as external_id), but also allows the PkId to be the IDENTITY column value therefore allowing existing code that relies on the IDENTITY column  through use of SCOPE_IDENTITY and @@IDENTITY to work as it used to.

The problem we've had is that we've come across a couple of queries that used to run in an acceptable time to now blow out completely. The generated query plans used by these queries is nothing like what it used to be before.

Given the new column is a PRIMARY KEY, the same data type as before, and PERSISTED, I would have expected queries and query plans to behave the same as they did before. Should the COMPUTED PERSISTED INT PkId essentially behave the same way as an explicit INT definition in terms of how SQL Server will produce the execution plan? Are there other likely issues with this approach that you can see?

The purpose of this change was supposed to allow us to change the table definition without the need to modify existing procedures and code. Given these issues, I don't feel like we can go with this approach.

FIRST

You probably don't need all three columns: old_id, external_id, new_id. The new_id column, being an IDENTITY, will have a new value generated for each row, even when you insert into external_id. But, between old_id and external_id, those are pretty much mutually exclusive: either there is already an old_id value or that column, in the current conception, will just be NULL if using external_id or new_id. Since you won't be adding a new "external" id to a row that already exists (i.e. one that has an old_id value), and there won't be any new values coming in for old_id, then there can be one column that is used for both purposes.

So, get rid of the external_id column and rename old_id to be something like old_or_external_id or whatever. This shouldn't require any real changes to anything, yet reduces some of the complication. At most you might need to call the column external_id, even if it contains "old" values, if app code is already written to insert into external_id.

That reduces the new structure to be just:

PkId AS AS COALESCE(old_or_external_id, new_id, -1) PERSISTED NOT NULL,
old_or_external_id INT NULL, -- values from existing record OR passed in from app
new_id INT IDENTITY(2000000, 1) NOT NULL

Now you have only added 8 bytes per row instead of 12 bytes (assuming that you aren't using the SPARSE option or Data Compression). And you didn't need to change any code, T-SQL or App code.

SECOND

Continuing down this path of simplification, let's look at what we have left:

The old_or_external_id column either has values already, or will be given a new value from the app, or will be left as NULL.
The new_id will always have a new value generated, but that value will only be used if the old_or_external_id column is NULL.

There is never a time when you would need values in both old_or_external_id and new_id. Yes, there will be times when both columns have values due to new_id being an IDENTITY, but those new_id values are ignored. Again, these two fields are mutually exclusive. So what now?

Now we can look into why we needed the external_id in the first place. Considering that it is possible to insert into an IDENTITY column using SET IDENTITY_INSERT {table_name} ON;, you could get away with making no schema changes at all, and only modify your app code to wrap the INSERT statements / operations in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements. You then need to determine what starting range to reset the IDENTITY column to (for newly generated values) as it will need to be well above the values that the App code will be inserting since inserting a higher value will cause the next auto-generated value to be greater than the current MAX value. But you can always insert a value that is below the IDENT_CURRENT value.

Combining the old_or_external_id and new_id columns also does not increase the chances of running into an overlapping value situation between auto-generated values and app-generated values since the intention of having the 2, or even 3, columns is to combine them into a Primary Key value, and those are always unique values.

In this approach, you just need to:

Leave tables as being:

PkId INT IDENTITY(1,1) PRIMARY KEY

This adds 0 bytes to each row, instead of 8, or even 12.
Determine the starting range for app-generated values. These will be greater than the current MAX value in each table, but less than what will become the minimum value for the auto-generated values.
Determine what value the auto-generated range should start at. There should be plenty of room between the current MAX value and plenty of room to grow, knowing at the upper limit is just over 2.14 billion. You can then set this new minimum seed value via DBCC CHECKIDENT.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

SECOND, Part B

A variation on the approach noted directly above would be to have the App code insert values starting with -1 and going down from there. This leaves the IDENTITY values as being the only ones going up. The benefit here is that you not only don't complicate the schema, you also don't need to worry about running into overlapping IDs (if the app-generated values run into the new auto-generated range). This is only an option if you aren't already using negative ID values (and it seems pretty rare for people to use negative values on auto-generated columns so this should be a likely posibility in most situations).

In this approach, you just need to:

Leave tables as being:

PkId INT IDENTITY(1,1) PRIMARY KEY

This adds 0 bytes to each row, instead of 8, or even 12.
The starting range for app-generated values will be -1.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

Here you still need to do the IDENTITY_INSERT, but: you don't add any new columns, don't need to "reseed" any IDENTITY columns, and have no future risk of overlaps.

SECOND, Part 3

One last variation of this approach would be to possibly swap out the IDENTITY columns and instead use Sequences. The reason to take this approach is to be able to have the app code insert values that are: positive, above the auto-generated range (not below), and no need for SET IDENTITY_INSERT ON / OFF.

In this approach, you just need to:

Create Sequences using CREATE SEQUENCE
Copy the IDENTITY column to a new column that doesn't have the IDENTITY property, but does have a DEFAULT Constraint using the NEXT VALUE FOR function:

PkId INT PRIMARY KEY CONSTRAINT [DF_TableName_NextID] DEFAULT (NEXT VALUE FOR...)

This adds 0 bytes to each row, instead of 8, or even 12.
The starting range for app-generated values will well above what you think the auto-generated values will approach.
Wrap app code INSERTs in SET IDENTITY_INSERT {table_name} ON; and SET IDENTITY_INSERT {table_name} OFF; statements.

HOWEVER, due to the requirement that code with either SCOPE_IDENTITY() or @@IDENTITY still functions properly, switching to Sequences is not currently an option as it appears that there is no equivalent of those functions for Sequences :-(. Sad!


Is it possible to change the family_guid in MSSQL? So I have a bit of a problem with some databases I'm trying to migrate to amazon RDS

We have about 20 Databases that are all derivatives of others that all share the same family_guid because they are based on backups of others. The problem is that amazon will not allow you to restore a DB that has the same family_guid of another even if they are two separate databases. This is where my question comes in, Is it possible to change the guid or am I out of luck and will need to do some exports/imports on these databases.

I have encountered the exact same scenario as you - many DBs all based off a template, and all need to go into RDS.

AFAIK there is no way to change the underlying family_guid in a simple command.
However, using the .bacpac export/import will assign a new family_guid to the imported DB. Sadly you can't directly import a .bacpac into RDS (the import fails, and you can't delete the database!)

So, as a work around, I ended up with the following process:

Create a .bacpac (contains both data & schema) of the original DB.
Import that .bacpac into a staging database (could be LocalDB, or another instance somewhere, or even the same instance as the original DB).
Create a traditional backup (.bak) of the staging DB.
Upload the backup to S3.
Run the msdb.dbo.rds_restore_database command on the S3 backup file.

Quite a process for what should be a simple task!


Improve Performance by Left Join and Group By Count - Postgresql I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN.

How do I improve the left join?

I am using the below index

create index session_opportunity_index on public.session using btree(opportunity_id,session_id)

Below is my query

  EXPLAIN ANALYZE

  SELECT session.session_id AS session_id ,session.opportunity_id AS opportunity_id,
  session.last_modified_at AS last_modified_at,count(distinct layout_feature.layout_id) AS no_of_layouts ,
  count(distinct layout_pad_data.pad_id) AS n,
  session.tags AS tags  FROM public.session
  LEFT JOIN public.layout_feature ON session.session_id = layout_feature.session_id
  LEFT JOIN public.layout_pad_data ON layout_feature.layout_id = layout_pad_data.layout_id
  WHERE session.opportunity_id=263945 GROUP BY session.session_id

  LIMIT 600 OFFSET 3

Below is my Query Plan

QUERY PLAN
Limit  (cost=910.74..921.45 rows=298 width=64) (actual time=8.219..9.243 rows=298 loops=1)
  ->  GroupAggregate  (cost=910.63..921.45 rows=301 width=64) (actual time=8.206..9.226 rows=301 loops=1)
        Group Key: session.session_id
        ->  Sort  (cost=910.63..912.58 rows=781 width=64) (actual time=8.184..8.214 rows=301 loops=1)
              Sort Key: session.session_id
              Sort Method: quicksort  Memory: 39kB
              ->  Hash Right Join  (cost=831.86..873.11 rows=781 width=64) (actual time=8.070..8.099 rows=301 loops=1)
                    Hash Cond: (layout_pad_data.layout_id = layout_feature.layout_id)
                    ->  Seq Scan on layout_pad_data  (cost=0.00..38.60 rows=660 width=8) (actual time=0.002..0.058 rows=660 loops=1)
                    ->  Hash  (cost=822.10..822.10 rows=781 width=60) (actual time=7.689..7.689 rows=301 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 23kB
                          ->  Hash Right Join  (cost=160.14..822.10 rows=781 width=60) (actual time=7.602..7.641 rows=301 loops=1)
                                Hash Cond: (layout_feature.session_id = session.session_id)
                                ->  Seq Scan on layout_feature  (cost=0.00..543.38 rows=29538 width=8) (actual time=0.002..1.716 rows=29538 loops=1)
                                ->  Hash  (cost=156.38..156.38 rows=301 width=56) (actual time=0.112..0.112 rows=301 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                      ->  Bitmap Heap Scan on session  (cost=10.62..156.38 rows=301 width=56) (actual time=0.028..0.070 rows=301 loops=1)
                                            Recheck Cond: (opportunity_id = 123)
                                            Heap Blocks: exact=4
                                            ->  Bitmap Index Scan on session_opportunity_index  (cost=0.00..10.54 rows=301 width=0) (actual time=0.024..0.024 rows=301 loops=1)
                                                  Index Cond: (opportunity_id = 123)
Planning time: 0.318 ms
Execution time: 9.351 ms

Your only hope is to get an execution plan that uses nested loop joins so that you don't have to read the whole layout_feature table.

CREATE INDEX ON layout_feature (session_id, layout_id);
VACUUM layout_feature;

The VACUUM is there to give you an index only scan. You have to keep the table well vacuumed for that to work.

Next, you should increase work_mem to get a hash aggregate rather than a sort + group aggregate (if that is possible here).

If one of the tables does not change often, or you don't need exact results, using materialized views can do a lot.


SQL Availability Group Network On a SQL Availability Group what IP/network carries the workload of moving changes to the secondary replica and how do you find this network?

In my understanding of your question:

to check where network packet will go look at results of ROUTE PRINT (Hit [Windows]+[R], type CMD, hit [Enter]). Read article on Technet.
with TRACERT x.x.x.x (where x.x.x.x is your secondary replica servers IP address) you will see a route that's packet travels.


difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2 Does the INDEX REORGANIZE index cause recompilation of the stored procs the same way as  INDEX REBUILD

Index reorganize does not touches statistics so there is no chance for causing recompilation. Since when index is rebuilt with full scan stats are also updated for the column this can trigger recompilation as statistics change.


SQL Server 2008, switching to simple recovery mode Currently our transaction log is 50 GB; impressive huh?

I want to switch to simple recovery mode and remove the transaction log entirely.

Do I need to stop any user activity and detach the database. Or can I simply go into management studio and change the recovery mode.  How do I then remove the file ?

Edit:

I switched the live server to simple recovery mode, and then performed a shrink file. Has worked perfectly so far. The file is now 1mb.

(I  am aware switching to simple mode will restrict recovery : I won't be able to go to s specific point in time, only to when the last back up was.)

What exactly do you mean by "remove the transaction log entirely"? I only ask because if you mean that literally the only possible answer is "You can't do that and have a working database".

This is a good article to explain the implications of changes to recovery mode and what it does for you.

Can't remember if all the steps are needed, but what I did last time was change recover mode to simple, carry out a backup then carry out a file shrink operation on the database (Right click on the database in the management centre and choose the option to shrink files that's in there somewhere)


Oracle: Tables and Views missing from the all_objects dictionary I have a couple of database instances that are having the curious issue where tables and views do not show up in the all_objects dictionary which is causing some issue with certain schema viewing applications. Not all are missing, but there is only like a dozen of the 130 or so tables/views that are in the schema. If you query all_tables or all_views, they all show up just fine. If you select/update/delete with those tables, it works fine.

Any one know of what types of things I should be looking at that would cause this?

BTW, I am not the DB administrator to this DB, I'm just trying to figure this out for our client. I don't have full access to the DB.

I tracked it down to an SCHEMA.ALL_OBJECTS view attached to the schema that was restricting the tables rather than using the SYS.ALL_OBJECTS view.


Auto increment char varying column prefixed with some characters I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.

Is there a way to achieve this?

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL.

If so, yes, what you describe is possible, but please, please don't do this.

A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g.

CREATE TABLE blah(
   id serial primary key
);

is actually shorthand for:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'::regclass)
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

You can use this knowledge to cast the nextval return to a character type, e.g.

CREATE TABLE thisiswrong (
    id text primary key default CAST(nextval('thisiswrong_id_seq'::regclass) AS text)
);

but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.

I have intentionally not shown how to generate a formatted field like PAT000001, but you can use any expression in a DEFAULT, not just a CAST. So look at the to_char function or the format function for how to do this if you insist.

The correct way to do what you want to do is not to do it. Get the application to display codes like PAT000001 when it sees a primary key value for patient_id like 1. The user never needs to know that you're just storing integers.

If you want other prefixes like PAT, DOC, etc, use a composite primary key, e.g.

CREATE TABLE saner_ish (
    categorycode varchar(3) CHECK (length(categorycode) = 3),
    patient_id integer default nextval('saner_ish_patient_id'::regclass),
    PRIMARY KEY (categorycode, patient_id)
);

(possibly with an enum type, an IN list, or whatever for validation).

Then in the application turn PAT00001 into ('PAT', 1) for queries, and reverse it for display to the user.


MySQL restrict specific login to single ip address i am trying to setup a mysql server with some logins that can be accessed from anywhere, and another login ( effectively root access ) from a single machine with a static ip. both machines have a single WAN address and are located in the same hosted server facility. the only firewall present is the one built into the operating system.

Host ( 81.1.1.1 / SERVER-4XP5PS5 / mysql_win64 5.5 / svr2012r2 ):

firewall port translation :

netsh interface portproxy add v4tov4 listenport=5001 connectport=3306 connectaddress=81.1.1.1 protocol=tcp
Advanced Firewall Rules->Add inbound open port 'MySQL5001' / port 5001 / tcp / All connection types

mysql remote login :

create user 'testuser'@'82.2.2.2' identified by 'testpassword';
grant all on *.* to 'testuser'@'82.2.2.2';

Client ( 82.2.2.2 / mysql_win32 5.5 / svr2012r2 ):

mysql.exe -h81.1.1.1 -P5001 -utestuser -ptestpassword
ERROR 1045 (28000): Access denied for user 'testuser'@'SERVER-4XP5PS5' (using password: YES)

it seems the mysql server on the host thinks the connection is coming from the local machine, but it is not, i am running the mysql connect command from the client machine.

any ideas how to get mysql to get testuser to connect through port 5001 while restricting testuser connections to ip 82.2.2.2 ?

note : it works fine if i lose the ip address requirement and replace 82.2.2.2 with %, but that defeats the purpose of what i am trying to achieve.

Thanks for the contributions - although i have discovered a work around. Instead of using windows firewall to do port translation, i can just change my.ini and set my desired port. and instead of creating a user i just create the grant :

grant all on *.* to 'testuser'@'82.2.2.2' identified by 'testpassword';


Multiple files per partition/filegroup? I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we will be also running reporting queries and queries that looks for trends over multiple day and weeks.

My current solution will use 70 days in daily partitions using a dedicated filegroup per partition. After the data moves beyond the 70 day mark, it will go into a weekly partition for 42 weeks which are also using dedicated filegroups per partition, then 12 month and then 6 years, all set up in the same fashion.

We need really high performance and massive scale up capabilities (PB+ range). In order to minimize rework down the line, I am thinking about using multiple files per filegroup/partition for the daily and weekly filegroups/partitions. 4 per daily and 2 per weekly to be exact.

This way we could potentially increase read/load throughput per partition, as well as increase maximal capacity of the partitions (don't ask why, but we are worried to actually need that level of capacity on certain days).

Has anyone ever done it this way and what were your results? Is there any reason not to do it this way other than management overhead?

All the weekly, monthly, and yearly partitions would be in the same database on same server (application design issues, but multi-DB could be an option if properly motivated. Multiple servers or instances are not desirable).

The partition breaks are currently being discussed and evaluated. I chose the values above based on information I received on query patterns. Different number of days is of course possible but I kinda like the 10 weeks' worth of daily partitions.

We do have a seriously high-end datacenter, well 2 actually. We are discussing the purchase of a converged solution specific for this platform and one other. I personally would like to see dedicated AFA's (All Flash Arrays) but there are a some bridges to cross before I can get my hands on those.

I am aware of Data Warehouse Fast Track solutions, but they will not work for us. For one, we will be doing OLTP mostly so the benchmark figures will not be representative for what we will get. Second, they don't scale-up enough (at the moment). Some of the elements that come from the reference architectures, will be used of course but the 'turn-key' SKU's won't be an option. I am an ex-MS PFE so those resources are the first I look at.

You can't work on the partition <=> files relationship. Partitions are just affected to a Filegroup. However, splitting your Filegroup into different files will help with performance.
The numbers I always use is to have the number of partitions per Filegroup set to the MAXDOP value. I got this looking at how SQL Server split the tempdb into different files and I did notice some improvement copying this behavior.

Regarding partitions, what you should do is split them on filegroups so that the ones that are no longer updated never move. We typically have a Hot filegorup in our DWH, and a Cold one. The cold one is Read Only and we only change that status on the day we move data from Hot to cold. You could do this without having to create one filegroup per partition. That just makes it harder to manage.


Export MySQL query log to JSON format Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?

In that case, go to McAfee audit plugin as it's compatible with MySQL from version 5.1 to latest GA. This plugin outputs in json nicely for you and you can filter nicely on the events you do and do not want.


SQL Server: no search results caused by noise words I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine.

I found out that using words which are usually in a stop list (like articles) disrupts or breaks the retrieval in a FullText search and return no results. Using only the important keywords returns the result as usual. AFAIK in older versions of SQL Server a different system than stop list were used. Although I can not find anything relevant in the FTData folder because it is either empty or commented.

On inspecting the database via SQL Server Management Studio I see that no stop list is configured in the selected database (under Storage). I tried adding a new one based on the system stop list but it made no difference to the behavior.

What can cause this behavior?

UPDATE

The used query:

SELECT ProductID FROM SearchTable WHERE CONTAINS(*, '"word"') AND
CONTAINS(*, '"noiseword"')

The results fail because in the search query the words are connected with AND. Using a noise word apparently returns always false so no item is returned.

I tried to change that behavior but it is not prevented by this:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO

I will try a rebuild which may help based on this and this article, as suggested by wBob.

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.

Use database;
ALTER FULLTEXT INDEX ON table
   SET STOPLIST OFF;
GO


SQL Server Management Studio 2014 - Show same .sql file in multiple tab groups In Notepad++, I can look at the same file in two side-by-side tab groups. In that software, this feature is called "Clone to Over View". Is it possible to do this in SQL Sever Management Studio 2014?

To my knowledge SQL Server Studio doesn't have a clone/compare feature. Having said that, it appears that SoftTree makes a product that integrates into SQL Server Studio allowing you to use Notepad++ as the editor (which looks very cool):

http://www.softtreetech.com/sqlassist/more_screenshots.htm

http://www.softtreetech.com/sqlassist/

Note: I have zero affiliation or investment with this company.

SoftTree looks to be a bit pricy. However, you can get a free plugin to run SQL code from Notepad++ here (I'm liking they way this looks and may have to try it myself):

https://github.com/gutkyu/NppDB


What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL? I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the transformations of the current ELT run. Once the current ELT run is complete I would like to make the switch by running something along the lines of:

start transaction;
drop schema prod;
alter schema dev rename to prod;
end transaction;

All queries issued outside of the ELT process are read only. How would MVCC handle this? What would happen to read only queries that were running when the drop schema command is issued? What would happen to read only queries issued after the drop schema command? What about after the end of the transaction?

DROP SCHEMA would attempt obtaining exclusive use of the schema first, so it would only actually manage to drop the schema when PostgreSQL is done retrieving data from ongoing queries. Further queries would likely be locked until the end of the transaction in your question.

Queries accessing tables in the prod schema may fail after this transaction is finished if they (queries or functions procedures) were compiled prior to the dropping and renaming of the schemas, but should be safe for queries from newly established connections.


Can anyone explain to me the concept "incarnation" in Oracle database in an easy-to-understand way? The Oracle documentation describe incarnation as:

  incarnation

  A separate version of a database. The incarnation of the database
  changes when you open it with the RESETLOGS option, but you can
  recover backups from a prior incarnation so long as the necessary redo
  is available.

But I still can't fully understand it. Can anyone explain it in a easy-to-understand way to me please?

Thanks,

Following is a short graphic which I will be using to explain what incarnation is used for in the answer:

                                    restore db    +-----+           +-----+     +-----+
                                    recover db    | 2>3 | --------> |  3  | --> |  3  | -->
                                    resetlogs     +-----+           +-----+     +-----+
                                                  ^       Incarn.      3           3
                                                 /        SCN #       400         500
                                                /
                                               /
             restore db    +-----+          +-----+     +-----+
             recover db    | 1>2 | -------> |  2  | --> |  2  | -->  ...
             resetlogs     +-----+          +-----+     +-----+  ^
                           ^       Incarn.     2           2     |    2
                          /        SCN #      300         400    |   500
                         /                                       |
                        /                                        |
        +-----+     +-----+     +-----+                          |
    --> |  1  | --> |  1  | --> |  1  | -->   ...                |
        +-----+     +-----+     +-----+  ^                       |
Incarn.    1           1           1     |     1                 |
SCN #     100         200         300    |    400                |
                                         |                       |
Backup   11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00
                                         |                       |
Restore/                                (1)                     (2)
Recovery

Basics

When an Oracle database is created it receives an initial incarnation number. The incarnation number is stored in the database itself, the control file(s) of the database instance and in the central RMAN catalog database (if you have one).

New Incarnations

Let's take a look at the above graphic. It is a slight modification of the graphic which Oracle uses to explain what incarnations are.
Reference: RMAN Media Recovery (Oracle Docs)

Archive Log Backups

Now let's assume the Archive Log backups are occuring on an hourly basis. Oh, and the datbase is in the ARCHIVE_LOGS mode, which allows us to backup and restore archive logs which enables us to recover the database to a certain point in time. We'll also assume that the database has received a FULL backup at 04:00 (a.m.) the same day. Further, the database has nevery been restored since it was initiailly created and the current INCARNATION# of the database is at 1.

SCN

We'll further assume that 100 transcations occur each hour so that at the time of the backup the SCN# will be a multiple of 100.

Time

The time is only here to increase the readability of the graphic. It plays a role in understanding why Oracle creates a new incarnation for a database that has been restored to a certain point-in-time.

Restoring the Database to Point-in-Time (1)

Somewhere slightly after 13:00 (1pm)  somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets of a bunch of RMAN commands to restore the database to that point-in-time our clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.

RMAN retrieves the FULL backup of the database and all Archive Log backups from  disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll-forward all finished transactions to the Point-in-Time and roll-back all unfinished transactions to the Point-in-Time, to ensure the database is in a consistent state.

Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:

ALTER DATABASE OPEN RESETLOGS;

Why?

Ok. If you look at the graphic, the SCN# for 13:00 (1 pm) in the original INCARNATION# 1 path is 300. We never reached the backup at 14:00 (2pm) which would have recorded the SCN# 400 in the backup. During the restore/recovery process, no backup is going to happen and (let's assume) if the restore process takes an hour, then no new data is going to be inserted. When the database is ready to go back online and the next backup occurs at 14:00 (2pm) then the backup will contain the SCN# 300. This is the same SCN# of the database as it was before the restore/recovery was initiated, which was recorded for the backup at 13:00 (1pm).

How Does Oracle Know Which Backup to Use?

This is why the INCARNATION# is set to a new value. It allows Oracle to discern between the Archive Log backup at 13:00 (1pm) with the following information:

INCARNATION#    1
SCN#           300
Time......... 13:00

...and the backup at 14:00 (2pm) with the following information:

INCARNATION#    2
SCN#           300
Time......... 14:00

Restoring the Database to Point-in-Time (2)

Let's assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.

RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:

INCARNATION#    3
SCN#           400
Time......... 16:00

...which would otherwise conflict with the backup of the database at INCARNATION# 2 and SCN#400 from 15:00 (3pm). But, because of the new INCARNATION# everything is fine.

Summary

That is the (albeit short) explanation of what the incarnations are used for. It allows RMAN (and the heroic DBA) to determine which path to take to restore and recover the database that has previously been backed up and restored more than once.

Next Up

This short explanation of incarnations is by no means complete. There are other factors that result in ORPHANED incarnations and OBSOLETE backups. Some of these topics will be discussed in a follow up answer either in this thread or in a separate Q & A.

For Example...

When looking at incarnations on a larger time-scale, then restoring and recovering may require you to set off a RESET DATABASE TO INCARNATION <number> before you can start recovering the database, because you might be restoring and recovering the database to a point-in-time which is located on the path of the INCARNATION# 1 and which may require you to restore an AUTOBACKUP copy of the control file, before you can start even thinking about restoring and recovering the database.

Stay tuned...


When do I need more than one Oracle Instance on the same server? I have one application server separate from an Oracle server (one physical server).  I have three instances on one Oracle Database Server (another physical server).

I want to have three environments to work in, Development, Test, and Production.

Should these be three Instances or three Schemas that use the same Instance?  Edit:  The server can physically handle it either way (i.e. has enough space, memory, and so on.)

You most certainly don't want to have dev, test, and production all sit in the same instance. I wouldn't even put them on the same server.

Having separate instances for environments that serve different puproses allows you to isolate them from the security and maintenance perspectives. For example, if your development database needs to be restored, you don't want to put your production environment offline for that reason. You'll probably also have stricter security in production.

Even performance-wise separate instances are useful while sharing the same physical (or virtual) machine. For example, by limiting memory_target on the development instance you will ensure that runaway sessions in development won't eat up all resources on the server.


Here is one idea:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4
from (
     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15
                               order by sample_date desc) as rn
     from (
         select h.*, trunc(to_char (sample_date,'MI') / 15) * 15 as min15
         from heats h
     ) T
) U
where rn = 1
group by start_date, stop_date;

In the innermost select, an attribute min15 is calculated. It is the 15 min period to which sample_date belongs

select h.*
     , trunc(to_char (sample_date,'MI') / 15) * 15 as min15
from heats h

At the next level, we enumerate the rows within each 15 min period, starting with the last one:

     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15
                               order by sample_date desc) as rn
     from (
         ...
     )

At the outermost level, we filter out all but the last in each 15 min period (where rn = 1)

We also pivot out the last values to an individual column. Since we get null for all but one, we aggregate to a single row with max:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4
from (
    ...
)
where rn = 1
group by start_date, stop_date;

I don't normally use Oracle, so I googled for how to determine which 15 min period a row belongs to. There are probably more elegant ways to do it, but it should give you a start.

Fiddle


MySQL Workbench - specify Server/User/Pass on command line? I am currently testing Privileged Access Management solutions. I have a need to open the MySQL Workbench with a command line that specifies the server name, and user/password of the privileged account that is being used for that session.

In MSFT SQL Server Studio, you can start it with '-S servername -U username -P password' There doesn't seem to be a straightforward way to do this using the MySQL Workbench.  There is a '-query' option, but I can't find anything that specifies how to fully start the app with those parameters supplied dynamically.

Can anyone suggest a way to do this with MySQL Workbench?

You can use the query parameter and give it the name of one of the connections you have defined (similar for the admin parameter). This will open a SQL IDE tab with that connection.

Instead of a predefined connection you can also use the usual command line params -h127.0.0.1 -uroot -P3306 in conjunction with the query and admin parameters.

For details how this is parsed look in wb_utils_grt.py (connectionFromString(connstr)).

Update:

The documentation says:

  --query [connection|connection_string]:
     Empty: Open a query tab and prompts for a connection.

  Connection: Open a named connection.

  Connection_string: Create a connection based on the entered connection string, which should be in the form $USER@$HOST:$PORT.


Hibernate Query Running Slow I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end.

This query is taking anything from 40-90 seconds the first time it is run. On subsequent executions however the query is returned in a fraction of the time (and I don't even see the database being hit, so assume that hibernate is caching it).

If I copy and paste the query from Enterprise Manager into a SQL client and run the very same query directly (even changing a few parameters) the query returns in a fraction of a second.

If I look at the performance tuning tab in EM I see that the time taken is primarily taken up with User I/O Waits (97.5%), and CPU (2.5%). Could this mean that the fetch size I am using in hibernate is configured at too small a value?

If there is any other information you might need to help me get to the bottom of this issue then please let me know.

=====

Additional information:

We do have an index on the table and I can see that it is being used as part of the execution of the query, unfortunately it isn't very readable but I am not sure how else to include it:

Id  Operation   Name    Rows
(Estim)     Cost    Time
Active(s)   Start
Active  Execs   Rows
(Actual)    Read
Reqs    Read
Bytes   Mem
(Max)   Activity
(%)     Activity Detail
(# samples)
0   SELECT STATEMENT                        1
1   . FILTER                        1
2   .. HASH JOIN RIGHT OUTER        2674    7223    1   +4  1   0           1M
3   ... TABLE ACCESS FULL   TOTEM_EQ_EXPIRYCODES    475     4   1   +4  1   481
4   ... HASH JOIN RIGHT OUTER       2674    7219    1   +4  1   0           399K
5   .... TABLE ACCESS BY INDEX ROWID    TOTEM_EQ_UNDERLYINGS    1   2   1   +4  1   1
6   ..... INDEX UNIQUE SCAN     TOTEM_EQ_UND_PK     1   1   1   +4  1   1
7   .... NESTED LOOPS                       1
8   ..... NESTED LOOPS      2674    7216    42  +4  1   0
9   ...... TABLE ACCESS BY GLOBAL INDEX ROWID   EQUITIES_MONTHLY_INSTRUMENTS    2671    1871    45  +1  1   8438    3517    27MB        24.44   db file sequential read (11)
10  ....... INDEX RANGE SCAN    EQ_MON_INS_UNDERLYING_INDX  2671    12  42  +4  1   8438    27  216KB
11  ...... PARTITION RANGE ITERATOR         1   2           8438
12  ....... INDEX RANGE SCAN    EQ_MON_RESULT_INSPT_UNQ     1   2   44  +2  8438    0   5403    42MB        75.56   Cpu (1)
db file sequential read (33)
13  ..... TABLE ACCESS BY LOCAL INDEX ROWID     EQUITIES_MONTHLY_RESULTS    1   3

Here are the Global stats:

Elapsed
Time(s)     Cpu
Time(s)     IO
Waits(s)    Fetch
Calls   Buffer
Gets    Read
Reqs    Read
Bytes
45  0.73    45  1   22980   9740    76MB

It turns out that the problem was to do with the index that was being selected as part of the query plan.

The query was accepting a range as part of its set of parameters. For reasons I won't go into now we were passing the range start and end date to be the same value.

When we were making the call directly we were hard coding these parameters and Oracle was deducing that they represented a single date, whereas when passing them via code the range was passed as bind variables.

This meant that Oracle selected to scan across all partitions in one case, and against a specific partition in the other.

We ultimately fixed the query by passing in just a single date parameter, since we realised we didn't need a range!

A rather peculiar situation admittedly!


Does PostgreSQL `CLUSTER` command also cluster extended storage? I'm trying to improve the locality of my data by using the CLUSTER command to periodically cluster records that share the same user_id.  My most important data is in extended storage.  Will extended storage be clustered also?

cluster command is a table rewrite process. So, yes it will cluster anything on the table, including extended storage.


What is the best way to query these tables? Inner Join? database: showsdb
tables:

shows  (id, date, venue, city, state, length, source, source2, rating)
songs (id, name)
shows_songs (shows_id, songs_id)

I want to be able to query the shows table and display all the songs from that show.  I also want to query the songs table and display all the shows where that song appears.
For example:
shows table has 2 shows.  3 songs played at each show.
songs table has 5 songs.  Some of those songs were played at show1.  some at show2.  some at both
show1 setlist: song1, song2, song3
show2 setlist: song2,song4, song5
Do I need a FK in one of these tables?  How would that look?  Or can this be done with joins?  I am not too clear on that either.
Thanks for your help and suggestions

This would show all shows, that run that ne song.
SELECT
    s1.date
    , s1.venue, s1.city, s1.state, s1.length, s1.source, s1.source2, s1.rating,
    s3.name
FROM
        shows s1
    INNER JOIN
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN
        songs  s3
        ON s2.songs_id = s3.id
WHERE s3.name = "Walk like an Egyptian"

Foreign keys are needed when you want to guarantee, that in the shows_songs  are only shows/songs that already exist, but usually that you will do at app level, but with a foreign key restrainto you can't enter false data.
another Option is a FOREIGN KEY with ON DELETE CASCADE for example between shows and shows_songs, this would delete an entry from shows_songs when the shows would be deleted.
But in your case you don't need a foreign key.
An INNER JOIN is then need when you need to retrieve data from different shows.name and songs.name, then you need always to join the bridge table before the second table.
And take a good look at Aggregation function
Because
SELECT
    s1.venue,s3.name
FROM
        shows s1
    INNER JOIN
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN
        songs  s3
        ON s2.songs_id = s3.id
WHERE s1.id = 1

Would result in
venue    name
venue1   song1
venue1   song2
venue1   song3
venue1   song4

Which could be made to a single row with GROUP_CONCAT
this is a small database, so you can test the queries and results quite fast


High-resolution CPU usage information for batches and statements I'm working on a tool that I use to evaluate the performance of SQL Server queries in a development environment.  I use, and highly recommend, tools like SentryOne's Plan Explorer, but my tool reports on queries submitted by application code.  So I use Extended Events.

One of the things I've found is that the resolution of CPU times reported for the sql_batch_completed and sql_statement_completed events is poor.  Even though the cpu_time units is microseconds, the numbers reported are multiples of 15 milliseconds.  That's much larger than the values I'm trying to measure, for a certain class of queries I work with.  (Duration values are fine.)

Does anyone know a way to get higher-resolution CPU times?  Maybe a server setting or a different field or event?

Durations made visible by SQL Server vary in their resolution and accuracy.

SqlWorldWide has an excellent answer showing some of the details.

Be aware of over-profiling SQL Server.  Just like in quantum physics trying to measure the duration of a query too accurately will decrease the performance of the query itself.  In other words, the very act of measuring undermines that which you are attempting to measure.  Measure the performance of a group of queries instead of trying to measure the performance of each individual statement.  If you need to understand why a single particular statement is running slowly, isolate that statement and evaluate it while letting the rest of the group run normally.  Being aware of how SQL Server operates can be tricky enough, let alone trying to see if a query duration was 6 or 600 microseconds.


Add AWS EC2 instance to existing Availability Group I currently have an AG with 2 replicas both running SQL Server 2016. These are both located on premise. I was looking to add a third node using a AWS EC2 instance and asynchronous replication.

Networking issues aside, is this an acceptable approach?

The only resources I can find on the internet appear to use distributed AGs but that would mean having a second EC2 instance in AWS

I assume it would be like this hybrid it: disaster recovery scenario but with AWS instead of Azure. There is an interesting point in the article about having your Active Directory available in the cloud.

I think you'd have to give some thought to how quorum would work. For example, what would happen if you did a manual failover to the cloud instance, then later your on-premises instances came back online?

HTH


Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000 I tried to add a linked server running on SQL Server 2008 R2 (10.50.4000) from SQL Server 2012 (11.0.5058).

I keep getting this error:

  The linked server has been created but failed a connection test. Do you want to keep the linked server?

Does anyone know how to solve this problem?

Are there any restrictions on building linked server between different versions of the database?

Okay, created this linked server using tsql code

EXEC sp_addlinkedserver
@server='xxxLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='xxxxxx',   -- add here your server name
@location='',
@provstr='',
@catalog='smpro5'  -- add here your database name as initial catalog (you cannot connect to the master database)
-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'xxxLinkedServer',
@useself = 'false',
@rmtuser = 'xxxxSqlUser',             -- add here your login on Azure DB
@rmtpassword = 'xxxxxxxx' -- add here your password on Azure DB

When open the server node and view all tables, get the "Failed to retrieve data for this request" error.

I can access the 2008r2 server via UI, without any trouble.

I do have one concern, that is my main database sits on Amazon cloud. Could this cause any problem?

Okay, thanks for all the answers guys. I think it is the problem caused by the firewall setting on the 2008r2 server. I am going to ask server admin to open that port for me.

Thanks to system administrator, it now works.

Special thanks to  Remus Rusanu, thank you for pointing me to the right direction !!!

As your administrator I'd like to confirm that the problem has been caused by firewall restriction. SQL port is opened so you can complete linked server setup.

Cheers, mate


How to select the set of last non-NULL values per column over a group? I am using SQL Server 2016 and the data I am consuming has the following form.

CREATE TABLE #tab (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1));

INSERT INTO #tab VALUES
    ('A','Q1',2,NULL),('A','Q2',NULL,'P'),('A','Q3',1,NULL),('A','Q3',NULL,NULL),
    ('B','Q1',5,NULL),('B','Q2',NULL,'P'),('B','Q3',NULL,'C'),('B','Q3',10,NULL);

SELECT *
FROM    #tab;

I would like a obtain the last non-null values over columns val1 and val2 grouped by cat and ordered by t.  The result I am seeking is

cat  val1 val2
A    1    P
B    10   C

The closest I have come is using LAST_VALUE while ignoring the ORDER BY which is not going to work since I need the ordered last non-null value.

SELECT DISTINCT
        cat,
        LAST_VALUE(val1) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val1,
        LAST_VALUE(val2) OVER(PARTITION BY cat ORDER BY (SELECT NULL) ) AS val2
FROM    #tab

cat  val1 val2
A    NULL NULL
B    10   NULL

The actual table has more columns for cat (date and string columns) and more val columns (date, string, and number columns) to select the last non-null value.

Any ideas how to make this selection.

Using the concatenation technique from The Last non NULL Puzzle by Itzik Ben Gan would look like this with your sample table and column data types.

select T.cat,
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val1 as binary(4))),
                     3,
                     4
                     ) as int),
       cast(substring(
                     max(cast(T.t as binary(2)) + cast(T.val2 as binary(1))),
                     3,
                     1
                     ) as char(1))
from #tab as T
group by T.cat;

Another way to write this query that divides the steps into CTE's to perhaps better show what is going on. It gives the exact same execution plan as the query above.

with C1 as
(
  -- Concatenate the ordering column with the value column
  select T.cat,
        cast(T.t as binary(2)) + cast(T.val1 as binary(4)) as val1,
        cast(T.t as binary(2)) + cast(T.val2 as binary(1)) as val2
  from #tab as T
),
C2 as
(
  -- Get the max concatenated value per group
  select C1.cat,
         max(C1.val1) as val1,
         max(C1.val2) as val2
  from C1
  group by C1.cat
)
-- Extract the value from the concatenated column
select C2.cat,
       cast(substring(C2.val1, 3, 4) as int) as val1,
       cast(substring(C2.val2, 3, 1) as char(1)) as val2
from C2;

This solution uses the fact that concatenating a null value with something results in a null value. SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)


Should my Postgres backup server be as beefy (IO, processor, RAM) as my master? I've set up a Postgres database on a server with 2 CPU cores, 4GB of RAM and an SSD volume (on top of RAID 10 of course). This is our (sort-of) "beefy" production database server (I can easily add more RAM, etc. when the time comes).

Now it's time to set up a backup Postgres server (not used for reads or anything else, just strictly a replication/backup). I don't want to have to fork out another $150/mo for the same exact setup for my backup server, and I know that a lot of the server resources used for a production database have to do with complicated queries, sequential scans, etc., so my thinking was that I could get away with a 512MB server with about 1/6 the I/O performance, and 1 CPU core.

Is that thinking correct, or does a replication/backup Postgres server typically need nearly the same specs as a production Postgres server?

Note: I'm using Postgres 9.2.4 with streaming (non-synchronous) replication on an Ubuntu 12.04.2 server.

It depends a great deal on your workload - the write volumes and write patterns on the master.

The standby is essentially doing continuous crash recovery. It reads write-ahead logs containing the changes the master made to the tables and applies that to its own tables. It does this using a single worker, so it doesn't benefit significantly from I/O subsystem concurrency.

The main thing a replica server needs is good random I/O performance. It doesn't need fast fsync() as much as the master does, unless you plan to fail over to it.

I'd start with a small machine, knowing that it's always easy to upgrade. If you do this, it's vital that you carefully monitor replication to make sure the standby isn't falling too far behind, or you make sure you have WAL archiving set up with abundant space to allow the standby to cope if it falls too far behind the master. Otherwise your replica will fall far enough behind that the master no longer has a record of all the changes required to get from where the standby is to the current master's state, and you have to re-initialise the standby. If you're using it for backups, you don't want this to happen.


SQL - find records that are prefixes of my string I have a table with a column named prefix storing 'aaaa'. My application has a long string, eg. 'aaaabbbbccccdddd'. I want to select all rows in which a particular column is a prefix of 'aaaabbbbccccdddd'. The length of the prefix is variable.

How can I do it in Postgres?

I tried

SELECT * FROM my_table WHERE '%' || prefix || '%' ILIKE 'aaaabbbbccccdddd'

but it does not match.

First, your way of using [I]LIKE is wrong:

string LIKE pattern

So, string first, pattern second.

Furthermore, as you are after prefixes only, the starting wildcard is not needed.  Something like this should work:

SELECT * FROM my_table WHERE 'aaaabbbbccccdddd' ILIKE prefix || '%';

A small proof of this:

WITH my_table (prefix) AS (
VALUES ('abcde'),
       ('abcd'),
       ('abcE'),
       ('bcde')
)
SELECT * FROM my_table WHERE 'abcdefghijkl' ILIKE prefix || '%';

 prefix
────────
 abcde
 abcd


Compressions vs shrinking a database Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?

In SQL Server, shrinking a database/datafile and compression are not the same.  Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database.  When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually have data to fill that space up.  The idea is that you'll eventually put something in there (but not always).  Shrinking removes this empty space.

Data compression, on the other hand, compresses your objects to reduce the space within the data file they take up.  There are a couple different types of compression, but what they all end up doing is reducing the physical space required by your tables and indexes.

Consider you have a 1 GB data file with 600 MB worth of actual data in it.  The two operations will have the following affects:

Shrink:  You will resize the data file down to 600 MB.  This is the limit, because you still need capacity for the data.
Compress: You will resize your data from 600 MB to some amount less (depending on how good the algorithms can compress it), but your data file will remain 1 GB.

To your second question, compression is more CPU intensive(it has to uncompress the data to make use of it), but uses less disk IO (fewer bits to pull off the disk).  It might affect your query performance if you are CPU constrained.  The only way to properly evaluate is to test.


Setting up SQL Server service broker from one database to multiple databases I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance.

I have looked at the following examples, however, they only show me how to setup a SQL broker service between two databases:

Conversation between Databases in SQL Server - Service Broker
Setup service broker between two databases in SQL Server

I would be grateful if anyone could provide any assistance on this.

In your code the  ReceiveOrderForProcess procedure in ReceiverDB2 should be looking at a queue called DestQueue2 rather than DestQueue. I think.