texts: How do tablespaces differ in DB2 from databases in MySQL or SQL Server?

texts:
In DB2, it seems there is a strong separation between what a database is and what a tablespace is. Where as in MySQL and MS SQL Server tablespace and database seemed to be used synonymously.

What are the differences, if any?


texts:
I have two table with the same structure, i want an sql statement to compare them and see any modified line, deleted or added

Table Product1 :

 ________________
|Product  | Price|
|Product1 | 10   |
|Product2 | 8    |
|Product3 | 9    |
|________________|

Table Product2 :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product2 | 8    |
|Product3 | 9    |
|Product4 | 13   |
|________________|

Table result :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product4 | 13   |
|________________|


texts: BAK files not created on Secondary or Primary

texts:
SQL Server 2014 with most DB in AG. Backup preference set to prefer Secondary.

Plan 1 backup all user DBs (AG and non AG)
Plan 2 backup all user DB trans logs

Both .bak and .trn files were created with no issues. Received error email from the one that did not finish job due to AG settings. But .bak files were created by one or the other instance.

These setting WERE WORKING fine up until 2 months ago.

Now for some reason, no .bak files at all for ANY AG database are created.

BAK: bak files for nonAG DBS are created by both primary and secondary. NEITHER the Pri or Sec are creating bak files for any AG Db.

TRN: The trn are created fine on secondary for all AG and nonAG DBs. The primary only has trn backups for nonAG. SO TRN backups are working the way they should.

So what changed?

I know that there were VM upgrades done during this time and I also installed the latest SQL SP.

The whole point of selecting prefer Secondary option is so backups happen on Sec, both bak and trn. Selecting ignore priority option in plan setting is NOT a solution it is a band-aid.

The only thing close to what is happening I found here https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/02/maintenance-plan-does-not-backup-database-or-log-of-database-defined-in-availability-group/
but that is also vague as it says

  the maintenance plan may cease to backup the database or log of that database

Seriously: may.

Prefer secondary means that backups should run on secondary, plain and simple but they aren't running on either anymore. Any ideas?


texts: SET IDENTITY_INSERT equivalent in Access

texts:
In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not able to insert value of my own.

This link shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

Now how the same can be done for Access database.If i delete a row having some autonumber,then in future i want to insert same autonumber value.But i am not able to use SET IDENTITY_INSERT for access. So how the same can be done in Access.


texts: restore table and populate new data from transaction log

texts:
I use mysqldump nightly to backup a database. I'm trying to find a way to do a partial restore on the data from one table. I use the --master-data flag so I know where in the transactions log my data was at the generation. Is there a command I can run for my one table to populate from X in the the logs to the current log position?

There already is a slave process running on this machine. I'm trying to create this as a second table with a similar name do run some tests with.


texts: Upgrade SQL Server 2012 Developer Edition to SQL Server 2014?

texts: I have several x64 SQL 2012 Developer servers with SP2 and CU4/CU5. In your experience, with this SP/CU mix, is the upgrade to 2014 Developer supported ? I can only find information on the supported path from 2012 SP1 to 2014.


texts: Postgresql permissions on schemas to groups

texts:
I want to have several users able to create schemas and tables. This schemas (and tables) should be available to all. I would like to setup the DB, with all permissions in place (and future permissions using ALTER DEFAULT PRIVILEGES). Users shouldn't have to change any permissions afterwards.

The schemas are created using a client software, QGIS in this case. So, users are only able to issue CREATE SCHEMA newschemaname.

The problem is, even if the users belong to the same group, they are not able to see each other schemas.

Unable to access other's schemas

I'm using two roles for testing. Users admin1 and admin2, both belonging to grp_admin:

CREATE ROLE grp_admin NOLOGIN;
-- one user should be able to create db and extensions; these permissions are not inherited
CREATE ROLE admin1 WITH PASSWORD 'test2k19' LOGIN inherit SUPERUSER CREATEDB CREATEROLE;
GRANT grp_admin TO admin1;
CREATE ROLE admin2 WITH PASSWORD 'test2k19' LOGIN inherit;
GRANT grp_admin TO admin2;

As admin1, I created a new database, and granted some permissions (and also permission for future objects):

CREATE DATABASE people;
\c people

-- To create schemas and tables
GRANT ALL PRIVILEGES ON DATABASE people TO grp_admin;

ALTER DEFAULT PRIVILEGES GRANT select, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO grp_admin;

If, as user admin1 I create a new schema and table, the other admin2 has no access to it:

create schema archive;
create table archive.vip ( thename varchar);
insert into archive.vip values ('Fernão de Magalhães');

As admin2:

select * from archive.vip;
ERROR:  42501: permission denied for schema archive
LINE 1: select * from archive.vip;

Able to access other's schemas

If I use authorization grp_admin when create the schema, it works. As user admin1:

create schema future authorization grp_admin;
create table future.vip ( thename varchar);
insert into future.vip values ('Robot 5126');

As user admin2:

select * from future.vip;
  thename
------------
 Robot 5126

The problem

Since users are creating schemas with  QGIS (a client application), issuing just CREATE SCHEMA newschemaname, users are not able to share each other schemas and tables.

How can I have this second admin2 accessing the admin1 created schemas? (Without issuing GRANT permissions after each schema created).


texts: Auto-complete in pgAdmin: how to get it to automatically appear?

texts:
It looks like
auto-complete is only activated after hitting ctrl + space https://www.pgadmin.org/docs/pgadmin4/4.12/keyboard_shortcuts.html

This is unexpected to me: not quite as "auto" as I would expect an auto-complete feature to be. Is there a way to avoid having to hit ctrl + space to activate auto-complete?


texts: Mysql improve Self join performance large table?


texts: Ways to speed up IN queries under PostgreSQL

texts:
I have a query of the following form:

SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000)

The IN query has anywhere from 10 to 25000 values. The query runs for minutes at a time. I have a backlog of nearly 500,000 queries like this to run.

The twitter_user_id column is indexed. Any ideas on how I could speed this up?

# \d twitter_personas
                                    Table "public.twitter_personas"
      Column      |          Type          |                         Modifiers
------------------+------------------------+------------------------------------------------------------
 persona_id       | uuid                   | not null
 twitter_user_id  | bigint                 |
 screen_name      | character varying(40)  | not null
 avatar_url       | text                   |
 hashval          | integer                | not null default nextval('personas_hashval_seq'::regclass)
Indexes:
    "twitter_personas_pkey" PRIMARY KEY, btree (persona_id)
    "index_twitter_personas_on_screen_name" UNIQUE, btree (screen_name)
    "index_twitter_personas_on_screen_name_persona_id" btree (screen_name, persona_id)
    "index_twitter_personas_twitter_user_id" btree (twitter_user_id) WHERE twitter_user_id IS NOT NULL


texts: Best way to delete very large recordset in Oracle

texts:
I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end.  The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store.

Every month we are required to purge records from the two of the main tables.  The criteria for the purge varies and is a combination of row age and a couple of status fields.  We typically end up purging between 10 and 50 million rows per month (we add about 3-5 million rows a week via imports).

Currently we have to do this delete in batches of about 50,000 rows (ie. delete 50000, comit, delete 50000, commit, repeat).  Attempting to delete the entire batch all at one time makes the database unresponsive for about an hour (depending on the # of rows).  Deleting the rows in batches like this is very rough on the system and we typically have to do it "as time permits" over the course of a week; allowing the script to run continuously can result in the a performance degradation that is unacceptable to the user.

I believe that this kind of batch deleting also degrades index performance and has other impacts that eventually cause the performance of the database to degrade.  There are 34 indexes on just one table, and the index data size is actually larger than the data itself.

Here is the script that one of our IT people uses to do this purge:

BEGIN
LOOP

delete FROM tbl_raw
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

This database must be up 99.99999% and we've only got a 2 day maintenance window once a year.

I'm looking for a better method for removing these records, but I've yet to find any.  Any suggestions?


texts: SQL Server 2008 R2 - Two Files Stuck In Restoring Status

texts:
Background: SQL Server 2008 R2 mirrored and clustered.
We recently failed over the databases via mirror and everything went smoothly. When it was time to do a differential backup, I got an error so I went to take a look. The error message is:

The backup of the file or filegroup "DB_Filegroup_Archive" is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. The backup of the file or filegroup "DB_Filegroup" is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8.
BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. BACKUP DATABASE is terminating abnormally. Cannot open backup device 'J:\SQL Backups\DB\Differential\DB_Differential_Backup_201508241903.bak'. Operating system error 2(The system cannot find the file specified.). VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Then I went to take a look at sys.database_files and the state_desc is RESTORING. I thought perhaps it was stuck so I attempted to cluster failover the database instance which fixed another issue (SQL Agent schedule not working), but this issue didn't get fixed.
This is what the error log states:

Filegroup DB_Filegroup_Archive in database DB is unavailable because it is Restoring. Restore or alter the filegroup to be available. Filegroup DB_Filegroup in database DB is unavailable because it is Restoring. Restore or alter the filegroup to be available.

I failed it back to the primary site and the files were ONLINE and great, but when it's on the secondary site, it's set back to RESTORING.  I don't understand what's going on...
Can someone please help me?


texts: Is there any risk in SELECT ... FOR UPDATE outside transaction scope in MySQL?

texts:
In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not.

My question is if it has any risk, or performance issue, or chance causing deadlock or something else naughty ?

I know that select for update can lock a row only inside a transaction and that is the common use case.

I provide below two concrete use cases, and after the select for update used regardless of inside of transaction or not.

Use case 1 - Just select a book and return it

book = booksRepository->getBook(7);
return book;

Use case 2 - Transaction, Select the book, check that is valid and if so update

bookRepository->startTransaction();
book = booksRepository->getBook(7);
if (//some condition with the book properties) {
   bookRepository->updateBookAuthor(7, 'bbb');
}
bookRepository->commitTransaction();

In both of the above examples, the function getBook inside the repository does select for update

SELECT * FROM BOOKS
WHERE id = :id
FOR UPDATE;

I asked the other devs and the answer to why they do this was "Because otherwise we would have to create two functions in the repository, one getBook and one getBookForUpdate, so we just select always for update"...

That answer does not provide any insight or explanation for where I can learn, so I am asking here.


texts: What does maintenanceMode -7 mean on a mongo primary?

texts:
I've recently promoted a new member to primary of a mongo replicaset.  Things seem to be running smoothly for several days and am not seeing any application symptoms of any problems.  However peeking at rs.status() today I noticed it's listing maintenanceMode -7.  I didn't think this should be possible as it's the primary.  I'm having trouble finding anything useful in the docs on what this means.

What does this mean?

    {
        "_id" : 26,
        "name" : "mongohost5:27032",
        "health" : 1,
        "state" : 1,
        "stateStr" : "PRIMARY",
        "uptime" : 106966,
        "optime" : Timestamp(1379627877000, 77),
        "optimeDate" : ISODate("2013-09-19T21:57:57Z"),
        "maintenanceMode" : -7,
        "self" : true
    }


texts: Optimizing queries on a range of timestamps (one column)

texts:
I am using Postgres 9.3 through Heroku.

I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ranges and those calls can take up to 40 seconds and would love to hear suggestions on how to improve that.

I have the following index in place on this table:

CREATE INDEX idx_traffic_partner_only ON traffic (dt_created) WHERE campaign_id IS NULL AND uuid_self <> uuid_partner;

Here is an example SELECT statement:

SELECT SUM("clicks") AS clicks, SUM("impressions") AS impressions
FROM "traffic"
WHERE "uuid_self" != "uuid_partner"
AND "campaign_id" is NULL
AND "dt_created" >= 'Sun, 29 Mar 2015 00:00:00 +0000'
AND "dt_created" <= 'Mon, 27 Apr 2015 23:59:59 +0000'

And this is the EXPLAIN ANALYZE:

Aggregate  (cost=21625.91..21625.92 rows=1 width=16) (actual time=41804.754..41804.754 rows=1 loops=1)
  ->  Index Scan using idx_traffic_partner_only on traffic  (cost=0.09..20085.11 rows=308159 width=16) (actual time=1.409..41617.976 rows=302392 loops=1)
      Index Cond: ((dt_created >= '2015-03-29'::date) AND (dt_created <= '2015-04-27'::date))
Total runtime: 41804.893 ms

http://explain.depesz.com/s/gGA

This question is very similar to another on SE, but that one used an index across two column timestamp ranges and the index planner for that query had estimates that were way off. The main suggestion there was to create a sorted multi-column index, but for single column indexes that doesn't have much of an effect. The other suggestions were to use CLUSTER / pg_repack and GIST indexes, but I haven't tried them yet, since I'd like to see if there is a better solution using regular indexes.

Optimizing queries on a range of timestamps (two columns)

For reference, I tried the following indexes, which were not used by the DB:

INDEX idx_traffic_2 ON traffic (campaign_id, uuid_self, uuid_partner, dt_created);
INDEX idx_traffic_3 ON traffic (dt_created);
INDEX idx_traffic_4 ON traffic (uuid_self);
INDEX idx_traffic_5 ON traffic (uuid_partner);

EDIT: Ran EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) and these were results:

Aggregate  (cost=20538.62..20538.62 rows=1 width=8) (actual time=526.778..526.778 rows=1 loops=1)
  Output: sum(clicks), sum(impressions)
  Buffers: shared hit=47783 read=29803 dirtied=4
  I/O Timings: read=184.936
  ->  Index Scan using idx_traffic_partner_only on public.traffic  (cost=0.09..20224.74 rows=313881 width=8) (actual time=0.049..431.501 rows=302405 loops=1)
      Output: id, uuid_self, uuid_partner, impressions, clicks, dt_created... (other fields redacted)
      Index Cond: ((traffic.dt_created >= '2015-03-29'::date) AND (traffic.dt_created <= '2015-04-27'::date))
      Buffers: shared hit=47783 read=29803 dirtied=4
      I/O Timings: read=184.936
Total runtime: 526.881 ms

http://explain.depesz.com/s/7Gu6

Table definition:

CREATE TABLE traffic (
    id              serial,
    uuid_self       uuid not null,
    uuid_partner    uuid not null,
    impressions     integer NOT NULL DEFAULT 1,
    clicks          integer NOT NULL DEFAULT 0,
    campaign_id     integer,
    dt_created      DATE DEFAULT CURRENT_DATE NOT NULL,
    dt_updated      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
)

id is the primary key and uuid_self, uuid_partner, and campaign_id are all foreign keys. The dt_updated field is updated with a postgres function.


texts: Windows Domain User with Admin privileges cannot access a drive with full permissions in psql

texts:
I have installed the standard PostgreSQL 9.5 from Enterprise DB with a domain-level postgres user who has domain-level admin group membership. I have explicitly added full permissions for this user to the D: drive of my Windows Server 2012 machine and explicitly granted full permissions on several root and subfolders on the same drive.

However, no matter what I try, I cannot get the command line version of psql to recognize any permissions for this user. And it seems to be exclusively about the psql command environment.

For example, this fails:

\o 'd:/database/tiger'

with

  permissions denied

even though I am logged in as the postgres domain admin user who owns the database and whose account is the service account for PostgreSQL.

If I simply type psql into a command prompt (either with or without selecting Run as administrator), I am prompted for a password, but the correct password for the domain postgres account is not recognized.

Is it possible that the PostgreSQL installation created a 'local' postgres account (and that is the one psql is expecting or running under)?

I can find no local accounts on the machine at all and all access to the local accounts admin tools is denied.

psql exhibits this behavior whether I start it from a normal windows command line dialog, or from the pgAdmin3 Plugins menu item.

Who is this guy that psql is expecting?


texts: I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?


texts:
I need some guidance with this query being too slow.

SELECT DISTINCT ON (id)
  id,
  views - lead(views)
  OVER (PARTITION BY id
    ORDER BY update_date DESC) vdiff
FROM videoupdate;

With 10 million+ rows it takes ~30 seconds. I have created a multicolumn index that reduced the original time from 1 minute. I want to see difference between views for each row partitioned by id. Some thoughts I had:

After table update create TABLE AS with the query and select from it.
Move old data to backup and shrink table.
Look up data warehouse?
Change database schema?


texts: Window function IGNORE NULLS workaround for PostgreSQL

texts:
With the following query I can use the LAG() function to repeat the last non null value of c column:

SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000)               open_time,
   coalesce(o, LAG(c) over w)                                                                          o,
   coalesce(h, LAG(c) over w)                                                                          h,
   coalesce(l, LAG(c) over w)                                                                          l,
   coalesce(c, LAG(c) over w)                                                                          c,
   coalesce(v, 0)                                                                                v,
   coalesce(close_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000 + ((60000 * 1) - 1)) close_time
from temporary_timeframe
window w as (order by datetime);

Getting the following result:

But I need to repeat the value of the c column while the current column value is null. I see that if PostgreSQL supports IGNORE NULLS attribute on window functions this would be solved. How to solve this without IGNORE NULLS?


texts: Where can I find the SQL standard document?

texts: Where can I find a legal copy of the ISO SQL 2008 standard?


texts:
I know this might not be a very good question to be asked in this site, but I really need some helps/hints to be directed to the right path.

I want to have failover strategy for 2 MySQL servers which are in Master-Master Replication mode. I have done the replication, and it's working just fine. Yet, when in comes to the failover strategy, I'm kind of clueless...

I've been reading on the internet over the past couple of days (I know this is a very complex procedure, and I could not master it in a week or so), but the more I read the more confuse I get. Basically, as I understood, I need to have a software to monitor the network and report any failure and another one to switch between two systems. Heartbeat apparently is the best in doing so, but in all tutorials I read, they are using many other software ( like MON for monitoring, or other software for dumping or getting backups) as well. Is it necessary to have them? Can one have fail-over strategy only using MySQL and HA tool like Heartbeat?

Each website is introducing different tool and ways, which they have their own pros and cons, and of course it depends on how I want my "cluster" to work, and how much high availability is important to my system. I am just considering pointing it out to my customers, and it's not going to the production phase any soon, since my customers running small businesses and don't care about loosing data that much, however, I wanted to dig into the concept by setting up a simple master-master replication where in case of failure of any servers, clients automatically commit changes to the standby server.


texts: How can I find the cause of pg_catalog.pg_attribute constantly being vacuumed?

texts:
autovacuum seems to constantly run against pg_catalog.pg_attribute for the past week+. This seems pretty odd since we aren't doing constant table changes.

How can I understand why it's doing this and how to stop it? I'm on AWS Aurora and PG10.


texts: Is it worth making a separate DATE table or not?

texts:
If I have a table like this:

my_table
=======================
id    date    some_data

Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So it would now look like this:

my_table
==========================
id    date_id    some_data

dates
==========
id    date

According to MySQL, date data type = 3 bytes. The date_id would need to be a max of MEDIUMINT (3 bytes) in order to get the same result. A normal INT or BIGINT exceeds the storage requirements of a DATE type.

A downside to a separate table is that the queries would be more complicated because I am joining another table.

Are there any other ramifications that need to be taken into account as to whether or not to split the date field into it's own table? My gut reaction is that it's not worth doing unless you are dealing with a small set of dates (small enough to use TINYINT or SMALLINT).


texts: Log and Data Drive Configuration in VM environment

texts:
Anyone have any good feeling on how to arrange the underlying disk configuration for a VM (Hyper-V) implementation of SQL Server vs installing straight up on the hardware?

For a native Install, best practices say to put the logs on separate spindles from the data spindes, but what about in a virtual environment? Whereas the VM has virtual disks, we could create separate log .vhd and data .vhd and put those on separate spindles as before, but why not just stripe all your available disks and put the log and data virtual drives on the same set? Hypothetically, the more spindles in the stripe the faster the response and more IOPS, so we could just put both files on the same set. The Host machine would be acting as sort of a SAN.

The server we have is moderately busy, no problem with IO performance (logs and data currently on separate spindles) or CPU or memory pressure. The goal is to have more flexibility in maintenance, being able to clone or move the VM elsewhere, and possibly put another VM on the box to better utilize the hardware. It seems that creating one large striped drive in the host machine would allow us the full use of all available drive space without much impact on performance. The alternative is losing out on about 1/3 drive space just to hold smallish log files.

Server config is Windows 2012 R2 OS, SQL Server 2016. 6 drives in RAID 10 for all of logs tempdb and data -OR- 2 drives in RAID 1 and 4 drives in RAID 10 for tempdb and data.

Much thanks!


texts: EXECUTE AS USER = 'dbo' - The user does not have permission to perform this action

texts:
I created a trigger that now works fine as result, but there is 1 line where I run a query:

DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);

The user that will trigger this, has no permission to execute something like

EXECUTE AS USER = 'dbo' ;

I receive the error:

The user does not have permission to perform this action.

How can I achieve this?

The whole trigger code can be seen in this question:
UPDATE Trigger with condition


texts: CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6

texts:
I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that the problem is with the 9.6 version, I did another upgrade to 10.6. It got better but still considerably higher than the CPU usage for the original 9.5 version.

I haven't changed the application code during the upgrade. How can I check the reason for the increased CPU usage and possibly fix it?


texts: How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code

texts:
In this query I want to select rows from Question table where Subject Code is X if not then Go for Y and so on as mentioned in the case statement.

select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number,
           SubjectCode,*
FROM       dbo.Question mcl
inner join Answer mclo
on         mclo.AnswerID = mcl.AnswereID
where      StudentID = 6588
and        mclo.SubjectCode = (case when mclo.SubjectCode ='X' then 'X'
                               else
                                 case when mclo.SubjectCode ='Y' then 'Y'
                                 else
                                   case when mclo.SubjectCode ='Z' then 'Z'
                                   else
                                     case when mclo.SubjectCode ='P' then 'P'
                                     end
                                   end
                                 end
                               end)


texts:
I am attempting to create a data model for a sports league

There are two entities in my domain. The first entity is a Manager and the second is a Team.

A Manager has

personnel id
first name
last name
birth date
birthplace

A Team has one Manager

If these are the design constraints, then how can we connect these two constraints?


texts: Postgres multidisk master replicating to single disk slave

texts:
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)?


texts: CrystalDiskMark Test Discovers Lopsided Throughput; What Setting Should be Changed?

texts:
I'm running time trials on a new Windows 2012 Server host with PureStorage FlashArray SSD LUNs attached. I conduct an initial test with CrystalDiskMark and discover write performance is substantially better than read performance.

-----------------------------------------------------------------------
CrystalDiskMark 3.0.1 x64 (C) 2007-2010 hiyohiyo
                           Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]

           Sequential Read :   339.675 MB/s
          Sequential Write :  1454.235 MB/s
         Random Read 512KB :   227.305 MB/s
        Random Write 512KB :  2313.737 MB/s
    Random Read 4KB (QD=1) :    10.719 MB/s [  2616.9 IOPS]
   Random Write 4KB (QD=1) :    74.720 MB/s [ 18242.2 IOPS]
   Random Read 4KB (QD=32) :   367.156 MB/s [ 89637.8 IOPS]
  Random Write 4KB (QD=32) :   175.918 MB/s [ 42948.7 IOPS]

  Test : 4000 MB [G: 87.9% (450.2/512.0 GB)] (x5)
    OS : Windows NT 6.2 Server Standard Edition (full installation) [6.2 Build 9200] (x64)

Is this expected for a FlashArray config or can it be configured at the controller or LUN level to be more optimal for reads than writes? The host will host a data warehouse with tables already in the 250 GB+ range.


texts: What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

texts:
I have the following table:

CREATE TABLE `wp_prize_draws_log_copy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `entry_date` date NOT NULL,
  `entry_time` datetime NOT NULL,
  `email` varchar(255) NOT NULL DEFAULT '',
  `ip` varchar(15) NOT NULL DEFAULT '',
  `user_agent` text NOT NULL,
  `lead_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL DEFAULT '',
  `last_name` varchar(255) NOT NULL DEFAULT '',
  `telephone` varchar(255) NOT NULL DEFAULT '',
  `postcode` varchar(255) NOT NULL DEFAULT '',
  `optin` tinyint(1) NOT NULL DEFAULT '1',
  `third_optin` tinyint(1) NOT NULL DEFAULT '0',
  `result` enum('OK','DUPE','BANNED','IP_LIMITED') NOT NULL DEFAULT 'OK',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=189370 DEFAULT CHARSET=utf8;

And I need to do the following query on it:

SELECT COUNT(*)
FROM (
    SELECT MIN(`id`) as `first_id`
    FROM `wp_prize_draws_log_copy`
    WHERE `result` = "OK" AND `optin` = 1
    GROUP BY `email`
) as `f`
INNER JOIN `wp_prize_draws_log_copy` AS `pdl`
    ON `pdl`.`id` = `f`.`first_id`
WHERE `pid` = %d AND `optin` = 1

This query gives me the number of optins that we the first ever of a given prize but the query takes 5+ seconds with the inner query taking in the region of 3+ seconds.

I've tried adding indexes but with no success of speeding up the query.

What indexes can I apply to the table to speed this query, if any? Or can this query be written differently to speed it up?


texts:
Created a transaction log shipping configuration between two SQL Servers (different VMs) using the built in wizard. Backup job runs on primary server, copy and restore jobs run on secondary server.

The restore job fails giving the following message (message is in job history):

  Error: The restore operation cannot proceed because the secondary
  database 'My_DB_Name' is not in NORECOVERY/STANDBY
  mode.(Microsoft.SqlServer.Management.LogShipping)

Under my secondary database Properties > Options, I see recovery model is set to Full. What do I need to do to make the restore job run?


texts:
Given table A and table B, such that rows in B reference a row in A...

CREATE TABLE A (Id INT, column1 INT)
CREATE TABLE B (Id INT, AId INT)

Rows in B must be consistent with one another. So, when inserting rows in B, I update A within a transactions, delete existing rows and then insert new ones...

BEGIN TRANSACTION
UPDATE A SET column1 = 1 WHERE Id = 1
DELETE FROM B WHERE AId = 1
INSERT INTO B (AId) VALUES (1)
COMMIT

Question is how to retrieve rows from B without returning rows half way through the above transaction.

SELECT a.Id, b.Id
FROM B AS b
INNER JOIN A AS a
ON a.Id = b.AId

Could the above transaction read elements from two different sets of Bs or do I need to add WITH (UPDLOCK) on A...?

SELECT a.Id, b.Id
FROM B b
INNER JOIN A a WITH (UPLOCK)
ON a.Id = b.AId

This works. The update transaction can't get going until the read query has been completed. But is it necessary?


texts: Designing a user authenication (Roles & Rights) module

texts:
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to only one group. A group can have "n" number of rights.

I also want to add password history to the database, as the user will be required to change their password based on a application setting (example, every 90 days).

I also want to log an event for each time a user logs in and out. I may extend this to additional events in the future.

Below you will find my first crack at it. Please let me know any suggestions to improve upon it, as this is my first time doing this.

Do you see any need for additional attributes for role-based security and constraints for the password rules/expiration periods?


texts: Which parameters and metrics to look at when CDC based replication is causing high load?

texts:
I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while the extractor is not running, which leads me to believe that the triggers registered by the CDC package are working just fine, without causing any significant load.

When the extractor is running, the load average of the database server can go as high as 60, but it's not that high all the time. Most likely the variance depends on the number of writes.

What configuration parameters and/or metrics of the database should I look into to identify the cause of this load? Tungsten seems to be simply running standard CDC procedures as outlined below.

Initial prepartion steps of the extractor:

Get a list of tables to extract from a table prepared by a setup script:

SELECT * FROM TUNGSTEN_SOURCE_TABLES

For each source table, get a list of columns to extract.

SELECT
  UNIQUE CHANGE_SET_NAME,
  PUB.COLUMN_NAME,
  PUB_ID,
  COL.COLUMN_ID
FROM TUNGSTEN_PUBLISHED_COLUMNS PUB, ALL_TAB_COLUMNS COL
WHERE
  SOURCE_SCHEMA_NAME = '{SERVICE_NAME}' AND
  SOURCE_TABLE_NAME = '{TABLE_NAME}' AND
  SOURCE_SCHEMA_NAME = COL.OWNER AND
  SOURCE_TABLE_NAME = COL.TABLE_NAME AND
  PUB.COLUMN_NAME = COL.COLUMN_NAME AND
  CHANGE_SET_NAME = 'TUNGSTEN_CS_{SERVICE_NAME}'
ORDER BY COL.COLUMN_ID

Create a subscription with DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION
Create a subscriber view for each source table with DBMS_CDC_SUBSCRIBE.SUBSCRIBE
Activate the subscription with DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION

Inner loop of the extractor while it's running:

Extend the subscription window with DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW
Select each subscription view to fetch any changes

-- Receiving changes for the first time
SELECT
  COLUMN1, COLUMN2, ...,
  CSCN$,
  COMMIT_TIMESTAMP$,
  OPERATION$
FROM VW_TUNGSTEN_{SERVICE_NAME}{INDEX}
ORDER BY CSCN$, RSID$

-- Receiving changes after the first window
SELECT
  COLUMN1, COLUMN2, ...,
  CSCN$,
  COMMIT_TIMESTAMP$,
  OPERATION$
FROM VW_TUNGSTEN_{SERVICE_NAME}{INDEX}
WHERE CSCN$ > :lastSCN
ORDER BY CSCN$, RSID$

Purge the subscription window with DBMS_CDC_SUBSCRIBE.PURGE_WINDOW

Environment:

Oracle Database 11g Release 11.1.0.6.0 - Production

According to my client, this box is 5 years old
open_mode: read write
log_mode: archivelog
platform_id: 10
platform_name: Linux IA (32-bit)

Tungsten Replicator 3.0.0


texts:
We're going to be implementing encrypted backups in SQL 2014 soon and I'd like to know the gotchas or lessons encountered by anyone else who has done the same.   I've read up at MSDN and elsewhere, created my master key and cert for backups in master db (and backed them up!).  We do prod-to-test refreshes occasionally so I'll create the prod cert there from the backup file.  None of our databases are TDE.

What do you recommend maintaining the cert & key backup files and keeping them secure?  What kind of problems have you had long term with administering the backups or restoring them during DR?

Just looking for tips to avoid the arrows that inevitably come with something new & different.  Paranoia level is high where recoverability is concerned.


texts: Cross join table row with generate_series

texts:
Currently I have
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
                     , timestamp '2021-01-07 22:00'
                     , interval  '1 hour') t(x)

Table definition
create table plans
(
    id       serial not null
        constraint plans_pk
            primary key,
    name     varchar  default 50,
    duration interval default '01:00:00'::interval
);

which gives me a cartesian product of the plans table and timestamp generated series.
I would like to introduce a specific duration for each plans row, so one row could have "1 hour", mean while the other have "30 minutes" and then generate each combination for every row. A CROSS JOIN here doesn't work, I tried:
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
                     , timestamp '2021-01-07 22:00'
                     , interval  plans.duration) t(x)

SQL Fiddle: http://sqlfiddle.com/#!17/97a12/10


texts: Calculate balance for every row by sequentially subtracting each row's amount from an initial sum

texts:
I'll explain the issue with an example.
A query that will select and show the Withdrawal Process I made and show the status of my deposit balance.

A table Deposit is created with columns TotalAmount, DepositDate.
Another table Withdrawal is created with columns WithdrawAmount, WithdrawDate.

So I use a SELECT query to select from both tables with formulae:
SELECT WithdrawAmount,
CASE WHEN ( TotalAmount - WithdrawAmount) = 0 THEN 'ZeroBalanceOops'
ELSE 'StillAvailableYAY' as 'Status'
FROM Deposit Inner Join Withdraw WHERE [WithdrawDate] between this month beginning and ending

So for this query, let's assume I have a deposit of 500 and will only deposit once. It works well if I only withdraw once this month and withdraw the deposit completely. The result will be showing like this:
| WithdrawAmount | Status          |
| 500            | ZeroBalanceOops |

However, it doesn't work if I withdraw more than once in a month and these withdrawals would make the balance = 0. Assuming the deposit is 500 again, the result would show like this :
| WithdrawAmount  | Status            |
| 250             | StillAvailableYAY |
| 250             | StillAvailableYAY |

The expected result is to have 'StillAvailableYAY' at first withdrawal then 'ZeroBalanceOops' at second withdrawal status but as it only compares 500-250 not 500-250-250 thus it will be always the 'StillAvailableYAY' status. Using SUM(WithdrawalAmount) does not give the result I want as it would cause the two withdrawals to have status 'ZeroBalanceOops'.
Any idea on how can I retrieve the previously selected query and include it into calculation?
Or is there any better way to do so?


texts: How to resolve duplicate constraint names in Postgres?

texts:
On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to resolve this name conflict through ALTER TABLE RENAME CONSTRAINT, but that yields an error:

table x has multiple constraints named "y"

Which sounds like in order to rename the constraint I first need to… rename the constraint.
So, how can I do that?
(Side note: the release notes for PostgreSQL 11 have "Fully enforce uniqueness of table and domain constraint names", alluding to this very situation, I suppose, as I discovered while preparing for an upgrade.)
Minimal steps to reproduce:
create table t1 (id uuid primary key);
create table t2 (id uuid primary key);
alter table t1 add constraint oops foreign key (id) references t2 (id);
alter table t1 add constraint oops unique (id);
alter table t1 rename constraint oops to oopsie;


texts: Hardening WITH ENCRYPTION option for stored procedure protection

texts:
Is there any way for make WITH ENCRYPTION option more difficult to bypass ?

I have to protect the stored procedures code and hide it from other sysadmin of the instance.
I can't manage it with permissions because, as I said, there are more than one sysadmins in the instance.


texts: How to return ids with specific values in another column

texts:
I have a table USER_ROLE with the format like this.

+--------+---------+
|USER_ID | ROLE    |
+--------+---------+
|1       | ADD     |
+--------+---------+
|1       | DELETE  |
+--------+---------+
|1       | VIEW    |
+--------+---------+
|1       | TRANSFER|
+--------+---------+
|2       | ADD     |
+--------+---------+
|2       | DELETE  |
+--------+---------+
|2       | VIEW    |
+--------+---------+
|2       | TRANSFER|
+--------+---------+
|3       | ADD     |
+--------+---------+
|3       | DELETE  |
+--------+---------+
|4       | VIEW    |
+--------+---------+
|4       | TRANSFER|
+--------+---------+

I want to find the USER_IDs that have all the below ROLEs.

ADD
DELETE
VIEW

So it should return 1 and 2 from above example.

I managed to get the results with below queries.

SELECT DISTINCT USER_ID
FROM USER_ROLE r1
WHERE EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'ADD' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'DELETE' AND r1.USER_ID = r2.USER_ID)
AND EXISTS (SELECT * FROM USER_ROLE r2 WHERE r2.ROLE = 'VIEW' AND r1.USER_ID = r2.USER_ID)

and

SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'ADD'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'DELETE'
INTERSECT SELECT USER_ID FROM USER_ROLE WHERE ROLE = 'VIEW'

Can you suggest any simple alternatives to these.


texts: Can a killed application process cause blocking of the server?

texts:
We use an application made by HPE which blocks completely from time to time. The application comes with Java 6, uses ojdbc6 and connects to Oracle 11.
HPE claims that when one of our 6 application servers dies when it runs a blocking update to a central table this causes the Oracle server to wait infinitely effectively rendering all servers defunct.
I can see the blocking statement (our DBA showed me) and can see that the connection pool of all servers grows until it hits a defined maximum. But I have doubts that Oracle does not detect the died connection.

Can someone confirm HPEs theory or give me a hint to further diagnose this ?


texts: UPDATE statement with and without SELECT keyword

texts:
I have been doing some testing on removing the SELECT keyword from this update:

Update  #tmpDriverTime
Set     WorkDay2 = (Select IsNull(Sum(PyblHrs),0)
From    spr_DriverTimeEntry
Where   spr_DriverTimeEntry.DrvrID = #tmpDriverTime.EmployeeNo
And     Convert(Varchar(12),dtwrkd,112) = Convert(Varchar(12),@StartDate_1,112))

From the testing I have done it looks like the same data is returned whether or not I have the SELECT keyword. I ran the code by our DBA to get his approval of removing the SELECT statement and he said I can not as removing the SELECT statement would cause the update to work differently but he can not articulate how it works differently. I have done some searching on the new and found a few MSDN pages where they run the UPDATE FROM command with out a SELECT keyword. So now I am confused, my testing and other examples I found on MSDN say I can remove the SELECT keyword but my DBA says no. Does the removal of the SELECT keyword affect how the UPDATE FROM works and if so how does it affect it?


texts: Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

texts:
I have a table lets say calendar

Table Calendar --> idCal (int) , numberOfFields (int)

And each Calendar has asigned a number of fields

Table Field --> idField(int), textField(text), idCal (int) *fk

Point is right now, each time an user register, he is assigned a calendar, once the numberOfFields is populated, I select that value and generate an insert query similar to:

INSERT INTO Field (textField, idCal) Values ("","idOfTheGeneratedCalendar") , ("","idOfTheGeneratedCalendar") ......

Until I have a number of rows equivalent to numberOfFields from the table Calendar. Where each idField(int) begins at 0, and its an autoincrement up to whatever the numberOfFields is

I do that for each user. Point is... is there a better way to do this without building huge insert queries with aprox 3000 values each using a for iteration? Should I be concerned?


texts: view corrupted when viewed using design feature

texts:
All,
I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view to the table being updated i.e. column A from the view was updating column D in the table instead of column A as it should have.

When I right clicked the view and selected "Design" from the menu I could see the problem was indeed that the sql statement generated was being written to update column D in the table with information from column A in the view.

I suspected a change to the view definition so I right clicked the view and scripted it out to see when it was last updated and when I did this the script generated was correct and completely different from the sql generated when I chose the "design" option. At this point I dropped the view and recreated it from the script. Now all is well.

Has anybody ever seen this? I've been working with SQL server since 2000 and have never come across this. We are using SQL2005 SP4 on Win2K3 (upgrading to 12 soon). Any insight would be appreciated.
-Paul


texts:
Does alter trigger hang if a table is locked by some DML operation
?
What kind of exception does it throw? Resource busy 0054 or some other exception...?  I have tried to reproduce this scenario but in vain.


texts: Recommended best practice for server naming

texts:
We're currently in the process of upgrading to SQL Server 2012, in the past all servers have been installed with a naming convention which contains a "-" in the middle i.e. TS-DBL01

I'm trying to convince people that it would be better to remove this is, but I'm starting to question if it really is best practice to change this to TSDBL0.

Any advice welcome


texts: Does psql print command results to standard error?

texts:
I am running COPY command through psql 9.1.15 as a cronjob to append data in csv file to table1:

psql -q -h $DB_HOST -d $DB_NAME -p $DB_PORT -U $DB_USER <<EOF
copy table1
from '${BUCKET}/${DATA_FILE}'
credentials 'aws_access_key_id=$AWS_ID;aws_secret_access_key=$AWS_KEY'
csv
EOF

I am redirecting stdout but not stderr. The idea is that if there is an error, it will be passed to cron and it will send me email.

However, I am getting from psql this stuff printed to stderr:

INFO:  Load into table 'table1' completed, 342 record(s) loaded successfully.

The psql command does return 0 as exit code, so there are no errors.

The question is, does psql print this stuff to stderr or does the database server (Amazon Redshift) pass this message to psql marked as error? I have a hunch that this is psql's fault, based on this post.

Why would you print successful message as error anyway?


texts:
I am trying to write a T-SQL query for SQL Server to pull some information from all the databases. I will then run this nightly and email the report to who it needs to go to.

I have two separate scripts which I want to merge so all the data is formatted nicely in a table.

SELECT
    db.name AS DBName,
    type_desc AS Filetype,
    Physical_Name AS Location,
    (size * 8) / 1024 AS SizeMB,
    DATABASEPROPERTYEX(db.name, 'Status') AS Recovery
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

This returns Name, Type, Location, Size and Status. I would like to merge it with this script:

SELECT
    Database_Name,
    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM
    MSDB.dbo.BackupSet
WHERE
    Type = 'd'
GROUP BY
    Database_Name

This produces a table with Name, Last backup and days since last backup.

The end result is one query I can run to produce a table with:

Name, Type, Location, Size, Status, Last backup & Days since last backup.

I will then try to work out a way to run this, export it as HTML and email it to myself every morning so I have a quick overview of my environment.

Thanks,
Rob


texts: Postgres 9.5 xlog fills all disk space in replication setup with replication slot

texts:
I have replication set up on Postgresql 9.5 using replication slot. I have one primary and one slave machine, both Ubuntu 14.04 with a cluster called main that is in a directory other than the default. On both servers separately the database worked as expected (before setting up replication), including access from another Ubuntu server.

On the primary, I have in the postgresql.conf:

wal_level = hot_standby
archive_mode = on
max_replication_slots = 8
archive_timeout = 60
max_wal_senders = 8
wal_keep_segments = 100
logging_collector = on

I added a replication user to pg_hba.conf:

host    replication     repuser         [iprange]         md5

I restarted the db and created a replication slot like so (as sudo -u postgres psql postgres):

SELECT * FROM pg_create_physical_replication_slot('repslot001');

On the slave machine, I started with the pg_basebackup:

sudo -u postgres pg_basebackup -v -h [ipofprimary] -U repuser -D [mydatadir] -R -Xs -P

I added to postgresql.conf:

hot_standby = on

My recovery.conf:

standby_mode = 'on'
primary_conninfo = 'user=repuser password=geheim host=[ipofprimary] port=[port] sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'repslot001'
trigger_file = '[datadirectory]/finish.recovery'
recovery_target_timeline='latest'

I restarted the db and everything seemed fine. I went home at this point.

The next day my master server had crashed because the disk was full:

PANIC:  could not write to file "pg_xlog/xlogtemp.11700": No space left on device

I googled around and found that this happens if the archive_command, as set in postgresql.conf on the master, is incorrect or missing.

But it is my understanding that when using replication slots I don't need an archive_command (and so, I didn't set an archive command). What mistake did I make?

If you need more information to be able to answer this, please let me know. But note that I need to continue my work, I am now trying if I can promote slave to master and clean up the old broken master. I'll make a backup of the data directory, which also has the logs. Also note that this is a test setup, I will not lose any data. Only when I'm confident I know how it works and how to set it up correctly will I start using replication in a production environment.

Thank you.

Edit.
The mistake I made was that I mixed up using archiving and using replication slots (thanks Frankie for your patience). I now have the following in my postgresql.conf - and it is working as expected:

wal_level = hot_standby
max_wal_senders = 8
max_replication_slots = 1
hot_standby = on

I removed the archiving settings archive_mode and archive_command.


texts:
Every so often, my company's BI team will request the today's execution of a job be skipped.  What I've been doing in the past, is going into the job schedule and manually changing the schedule's start date to tomorrow.  Then, I click on OK on the job schedule to save the schedule edit and then OK on the job properties window.

I then see that the Next Run value change from today at noon to tomorrow at noon which is the desired behavior.  Instead of doing it manually, I'd like perform the same task using a script.  I tried updating the active_start_date column for the schedule in [msdb].[dbo].[sysschedules] but the job ended up running at noon and it had to be manually stopped.

Does someone know how to effectively skip the next execution for a job through the use of a script?

Environment: SQL Server 2012 SP1


texts:
I have an on premise oracle 11g database that i have to migrate to an EC2 instance hosting Oracle 11g in AWS. I have three options to achieve this

Use Oracle Data guard to replicate the on premise database to cloud database
Using RMAN to backup the existing database and restore it on Cloud
Using expdp and impdp commands to export and import the database

My database size is approximately 2 TB.
I have following doubts
Q1: What is the best way among the three options?
Q2: Using data guard means that the AWS and the on premise database should be connected the entire time until the data is replicated on cloud, what if there is a network disconnect in between the process? Do i have to restart the replication process or will it continue from where it stopped?
Edit:
We have a 2 day downtime window in which the data has to be migrated and restored on cloud. The on premise database is hosted on Solaris Operating System and Operating System on the AWS cloud will be RedHat Linux since Solaris in not available in AWS.
Will Oracle Data guard or RMAN support the change in the Operating system on the servers?


texts: How to create a user with a particular user_id?

texts:
Is it possible to specify the user_id of a user while creating it?

SQL> create user user1 identified by user1;

User created.

SQL> select user_id, username from all_users where username='USER1';

   USER_ID USERNAME
---------- ------------------------------
        72 USER1

SQL>

Instead of 72, can it be a number that I specify?


texts: Converting a string to an object reference

texts:
Sometimes I end up in a position where I have a string version of my object reference like so:

'server.database.schema.table'

And I need to use it as an actual object reference:

'server.database.schema.table'+'.'+[column]

How do I turn that string into:

[server].[database].[schema].[table]

so I can actually use it?

I'm sure this has been asked before, and it's probably newbish, but I just can't find the right keywords.  I'm not using it in an EXEC EXECUTE.

Per the comments, here's an example dataset:

CREATE TABLE #example
(
    Server NVARCHAR(255) NOT NULL
    ,[Database] NVARCHAR(255) NOT NULL
    ,[Schema] NVARCHAR(255) NOT NULL
    ,[Table] NVARCHAR(255) NOT NULL
    ,Field1 INT
    ,Field2 DATE
    ,Field3 VARCHAR(50)
)

INSERT INTO #example
(Server,[Database],[Schema],[Table],Field1,Field2,Field3)
VALUES ('LinkedServer','DB','dbo','TableA',15,CONVERT(DATE,'1-1-18'),'Text')

So, if I wanted to select the reference of the first row, I'd imagine I'd do something like:

DECLARE @SERVER ??, @DB ??, @SCHEMA ??, @TABLE ??

SELECT TOP 1 @SERVER = Server FROM #example
SELECT TOP 1 @DB = [Database] FROM #example
SELECT TOP 1 @SCHEMA = [Schema] FROM #example
SELECT TOP 1 @TABLE = [Table] FROM #example

SELECT * FROM
    @SERVER.@DB.@SCHEMA.@TABLE
WHERE
    Field1 = (SELECT TOP 1 Field1 FROM #example)
    AND Field2 = (SELECT TOP 1 Field2 FROM #example)
    AND Field3 = (SELECT TOP 1 Field3 FROM #example)


texts: Disable database tables for writes but report writes as successful

texts:
I manage a database where thousands of GBs of useless log data is created daily.

I can't fix the software that is performing all of this (unnecessary) logging. Is there a way to lock (or disable writes to) this log table so it's never written but at same time it will report the write as successful so it doesn't cause issues with the software?

I know I can just truncate the tables with a cronjob but I'd like to know if there is a better approach?


texts: When trying to configure data guard broker I'm getting Error: ORA - 12154 : TNS: could not resolve the connect identifier specified

texts:
I'm trying to setup data guard broker in my production instance. I have duplicated the primary database to standby database successfully. I've added primary database to data broker configuration and when I'm trying to add standby database following error is getting

DGMGRL> ADD DATABASE 'orcl_standby' AS CONNECT IDENTIFIER IS orcl_standby maintained as physical;
ERROR: ORA - 12154 : TNS: could not resolve the connect identifier specified

Tnsping from primary db to standby db and vice-versa is successful. I'm able to connect primary db from standby db using sqlplus sys/password@orcl as sysdba but when trying to connect to standby db from primary db, error is getting like

  ORA - 12154 : TNS: could not resolve the connect identifier specified.

Tried out all possible solutions.

Below is my tnsnames.ora file

    ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.42)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (SID = orcl)
      (UR = A)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL_STANDBY=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.44)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_standby)
      (SID = orcl_standby)
      (UR = A)
    )
  )

Any help will be appreciated


texts:
I've recently started my role as a DBA and my initial task has been to take ownership and redeploy a database provided by a 3rd party to help us conform to GDPR legislation.

As a consequence, I've not really been able to look at much beyond the project itself. I'm now at the stage where my database is ready to go live, although there are a few caveats.

My existing code for some aspects creates large temporary tables (70m+ rows) and I know my dev database has grown over this project.

My question is for this and subsequent projects working with large datasets, in instances of temporary storage, should I use the temp database or create staging tables within the database I migrate to and drop them?

The reason I ask this is my live tempdb currently is less than 1gb in size  whereas dev reached around 30 and should I continue with temporary tables, I would want to grow tempdb in advance to prevent waits on autogrowth.


texts:
I have a query which involves a full-text search like this:

SELECT TOP 30 PersonId,
              PersonParentId,
              PersonName,
              PersonPostCode
FROM dbo.People
WHERE PersonDeletionDate IS NULL
      AND PersonCustomerId = 24
      AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND PersonGroupId IN(197, 206, 186, 198)
ORDER BY PersonParentId,
         PersonName;

This generates two main plans, one is very fast in all cases, the other is very slow in most cases.

I have experimented with this query such that the FT search is not included and what I found is that the row estimates are always way lower than they should be.

If I run update statistics...with fullscan I still see extremely inaccurate row estimates from NC index seek operations in the execution plan.

When the row estimates are low enough, a loop join is selected, which is normally very slow (30+ seconds). Higher estimates seem to produce a good plan involving a merge join instead of a loop join.

Why is SQL Server still not estimating the rowcounts despite still having up to date statistics?

The plan: https://www.brentozar.com/pastetheplan/?id=rkXtE0jzX

When I remove the CONTAINS part, thereby omitting the FullText search, the query is fast, but the row estimate for the index seek is still 1 estimated, 2195 actual.

On @Kin's advice, I used CONTAINSTABLE, which ran instantly and produced the following plan: https://www.brentozar.com/pastetheplan/?id=S1hKainzQ
Interesting that there is no Full Text search operator.

Containstable requires RANK to produce the same result set in this case I've used AND RANK > 0 in the WHERE to produce the results I want, which produces this plan: https://www.brentozar.com/pastetheplan/?id=B1U7AA2zm

My only question now is about why row estimates are still inaccurate but I care less now that my FT queries seem significantly faster and more reliable. Very pleased!
https://www.brentozar.com/pastetheplan/?id=B1U7AA2zm

@EvanCarroll stats histogram here: https://pastebin.com/p7s0NvX5

Some follow up info - before/after execution plans for some typical FT search queries for the application being supported

A

Before: https://www.brentozar.com/pastetheplan/?id=SJlAAAN7X (5 seconds)
After: https://www.brentozar.com/pastetheplan/?id=H1ltkkSmm (<1
second)

B

Before: https://www.brentozar.com/pastetheplan/?id=Sy-gxJBQm (40
seconds)
After: https://www.brentozar.com/pastetheplan/?id=Sy2VxJrm7 (1
second)

C

Before: https://www.brentozar.com/pastetheplan/?id=r1z5e1rQ7 (2
seconds)
After: https://www.brentozar.com/pastetheplan/?id=r1oplkSQm (<1
second)

D

Before: https://www.brentozar.com/pastetheplan/?id=B1kHf1BQQ (2
minutes 20 seconds)
After: https://www.brentozar.com/pastetheplan/?id=r1D5z1SQm (11
seconds)


texts: Ola Backup script is picking up the cluster as the host

texts:
I have a SQL server that has local databases and some databases in an AG group and getting an error "Unknown host " when I run the following Ola backup script.
EXECUTE [dbo].[DatabaseBackup]
  @Databases                   = 'USER_DATABASES'
  ,@BackupSoftware             = 'DATA_DOMAIN_BOOST'
  ,@BackupType                 = 'FULL'
  ,@ChangeBackupType           = 'Y'
  ,@CheckSum                   = 'N'
  ,@CleanupTime                = 336
  ,@Compress                   = 'N'
  ,@DataDomainBoostDevicePath  = '/ddboost_path'
  ,@DataDomainBoostHost        = 'ddboostbackupsvr.mofo.com'
  ,@DataDomainBoostLockboxPath = 'C:\Program Files\DPSAPPS\common\lockbox'
  ,@DataDomainBoostUser        = 'ddbost_user'
  ,@NumberOfFiles              = 4
  ,@Description                = 'Scheduled Daily Full Backup'
  ,@Encrypt                    = 'N'
  ,@LogToTable                 = 'Y'

I determined that when it found an AG database, it would return the windows cluster name as the host name for the DDBoost command instead of the SQL Server instance name.
 IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1
  BEGIN
    INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)
    SELECT name AS AvailabilityGroupName,
           0 AS Selected
    FROM sys.availability_groups
...
          SET @CurrentCommand += ' -c ' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar) END

Is this a bug?  Why would we want to pickup the Windows cluster name instead of the actual SQL instance name?


texts:
I have the database structure:

|     Tag     |         | Article_Tag |         |    Article    |
---------------         ---------------         -----------------
id                      tag_id                  article_id
name                    article_id              title
                        type (numeric)

In a search field I'm entering various tag names and the search must return the Articles which contain those tag (or at least a part of them).

The type field it's a numeric value representing the weight of the tag in it's article.

The query that I'm using now for solving this problem is:

SELECT DISTINCT sum(article_tag.type), article.id, article.title FROM tag JOIN article_tag ON tag.id = article_tag.tag_id JOIN article ON article.id = article_tag.article_id WHERE [tag.name LIKE ? OR] x n GROUP BY article.id ORDER BY sum(article_tag.type) DESC LIMIT ?

The [tag.name LIKE ? OR] x n represents that tag.name LIKE ? OR will be repeated for the n tags entered in the search field. (For example the "sailing adventure ocean" string will be splitted and the three words will be compared with the tag.name).

The ordering by sum is required because we need some kind of "most relevant articles" feature.

The tag.name field is indexed and the DB Vendor is MySQL.

The Problem

My concern is linked to the scalability of this structure. I don't think that having a large number of rows in the Tag table and lots of OR clauses in that query will lead to a good response time. I ran some tests and the results were acceptable but I'm wondering if there is another solution for my problem. Maybe NoSql?

Thank you!


texts: MongoDB: Privileges on objects created by me only?

texts:
Is it possible to define a custom role, or otherwise assign privileges to allow a user read/write access only on objects that they have created?

Imagine 3 applications, each having its own login. For example: The recipe_app uses recipe_user to access the MongoDB store, and the lizard_app uses lizard_user to do the same. Part of the way the applications work is that they create and drop their own databases as necessary.

At any given time there may be only a northern_recipe database, a southern_recipe database, and a western_lizard database, or only an eastern_recipe database and a northern_lizard database (or any other permutation), and what will exist when is not foreknown.

Is it possible to set up recipe_user's privileges to only be able to read/write the recipe_* databases? Let's assume that the naming conventions stay as consistent as those in the example.

I thought about adding a tag with the creating user's name to each collection on import, and then a "redaction" filter to limit its access, but I don't like

that what I want to filter on is properly metadata, and shouldn't be mixed in with the data, and
that it's not really addressing the issue at the database level.

UPDATE: Inspired by this question about dropping collections based on a regex, I'm thinking running an admin script like this on a regular basis would achieve what I want, but with some delay between a database's creation and effecting the grant. (Please note this is tested but makes no presumption of being optimized.)

db.runCommand({listDatabases:1})['databases'].forEach(
  function(d) {
    if (d.name.match(/lizard$/)) {
      db.grantRolesToUser(
        "lizard_user",
        [ {role: "readWrite", db: d.name} ]
      )
    }
  }
)

MongoDB 3.4.0


texts: How do I configure SQL Profiler to show me queries to the views sys.databases and sys.tables?

texts:
I have a simple C# app that iterates through all databases on a server examining their schema. As such it performs a SELECT name from sys.database, iterated through those and does a SELECT name from sys.tables WHERE name='Something'. Then if it finds the table its looking for it does some queries, and does stuff with the data.

The connection string has an application name. If I setup a trace with a column filter on that application name looking for start/complete transaction, start/complete stored procedure, and complete statement. I see the queries I perform on the actual database tables, but not the queries to sys.databases and sys.tables.


texts: Mongodb - Returning the points nearest to a location, with distance

texts:
What is the MongoDB's equivalent to this query:

SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2)
                               + pow(106.772835-`Longitude`, 2)
                                 * cos(-6.186753*0.017453292519943)
                                 * cos(`Latitude`*0.017453292519943)
                             )
       as distance
from tablename ;

This uses the haversine formula to compute the great-circle distance to a fixed point. We want to get nearest 20 points from a location and then display the distance.


texts:
Does it ever make sense to have a clustered and non clustered index for the same column

I inherited a database and a web service that goes with it.

Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and sorted the same way). One is clustered and one is not, that is the only difference I see.

Does it ever make sense to keep both of these?
If I delete one which one should I delete


texts: ORA-01017: invalid username/password; cannot login with other users except sys user, Oracle Database 12c?

texts:
I have setup  Oracle Database 12c on my local machine. It was working fine initially but now I cannot login to users' account except the sys user. I have altered users' passwords and tried logging in using SQLPlus and SQL DEVELOPER.
ALTER USER XYZ IDENTIFIED BY XYZ;

I have also checked the parameter SEC_CASE_SENSITIVE_LOGON which is set to FALSE.
The users, that I am trying to connect are created in pluggable database i.e orclpdb and they do exist in the pluggable database.
I can only login to sys user of pluggable database and root.
The username and passwords are correct, I don't know why the oracle is behaving weirdly.


texts:
In a sql server database with a full model backup with tran logs backed up every 15 minutes, but ,still, the log file is huge. The backups (once a day) of the db and the tran log (every 15 minutes) are regular
the log space is almost empty ... then is it good to shrink it a little?


texts: What is the guideline for how much free space to keep available on a MySQL AWS RDS instance (SSD)? In the past for an on-premises SQL Server install we used to use something like 2x the largest table in the database as this would provide enough space for maintenance tasks etc. and plenty of reaction time for unexpected growth. Are there any such guidelines for a MySQL RDS install? (We were recently caught running far too lean and a few poorly constructed queries led to an insufficient space message).


texts: Unable to find "Local" window while debugging SSIS package

texts:
I'm trying to debug my package in SSIS. I need to see the value of my variables while debugging, but the problem is that I cannot find the "Local" window. The only window I have is the breakpoint window as you can see below:

What is wrong with my Visual Studio? (I'm using Visual Studio 2013)


texts: Where does the stored procedure Searchinall come from?

texts:
When I create a new database in SSMS either using the creation wizard or the CREATE DATABASE command the database will contain a stored procedure called Searchinall. This procedure has never been added to new databases previously, but since 2 days it will be created in every new database, no matter how I create it. I have tried creating a database from a different Computer on this server as well, but the stored procedure will still appear.
When I tried looking online for a procedure Searchinall I could not find any results, so I suspect it is not a new feature and nothing that should be created by default.
Could someone have maybe accidentally added it as a default stored procedure to be created in every new database on the server?
If so, how can I disable it?


texts: What is the difference between select and fetch time?

texts:
In this article MySql vs MongoDB performance benchmark, they defined two measurements:

select time
run time (select + fetch)

I do not understand what the difference is between these two. Can someone explain what the select time and fetch time are?


texts: Need some help in Group by StateID - TSQL Query

texts:
Can anyone help me on below scenario I need small help on group by state ID

Below is my query and I am tried to get all the out put in single line .

But I am getting different lines for each year . please help me to get all the output in single row as the State ID is same.

SELECT  StateID,
SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
(Select count(Fileid) where year = '2016' ) AS TotalFs2016,
SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
(Select count(Fileid) where year = '2017' ) AS TotalFs2017,
SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
(Select count(Fileid) where year = '2018' ) AS TotalFs2018,
SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
(Select count(Fileid) where year = '2019' ) AS TotalFs2019
--FileState
FROM
(
SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
FROM #Temp1
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp2
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp3
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp4
)t
where StateID like '570'
GROUP BY
t.StateID , year

Current out put gives 4 rows for different years like 2016 , 2017 , 2018 , 2019 along with the Size of the files.

Where are I want to get only one row as put with all years data as the ID is same.

I tried to GROUP BY but still does not help .

Thank you for the help.


texts: Connection Issues with Informix OLEDB Provider

texts:
I've been trying to establish a OLEDB Connection to an Informix Dynamic Server. (Version IBM Informix Dynamic Server Version 12.10.FC12WE)

Background Info:
A program, which will be used by my company, requires OLEDB and doesn't support ODBC. The colleague,who is currently tasked with managing the implementation, has a lot of other work piled up and so the implementation will be delayed. In the meantime, I've been tasked to figure out how to properly configure the Windows 10 Client PC (which will run the program) for the connection. I have very little experience with databases, so I've searched for about 2 weeks for answers in various documentations and forums. So far, I had mixed success.

What has already been done:

The coledbp.sql- script has been run against the sysmaster table on the database
Informix Client SDK and Microsoft Data Access Components have been installed on the Client PC.
Using setnet32 the registry entries for host:port have been set.
The driver ifxoledbc has been registered.
ODBC-Datasources (both 64-Bit and 32-Bit) have been created and tested (they work fine).

Various Tests

For all my tests I've been using the Connection String:

  "Provider=ifxoledbc;Data Source=mydatabase@myserver;User ID=myuser;Password=mypassword;

First I tried to test the connection with an .udl-File. I select the IBM Informix OLE DB Provider and enter the data source and userdata. When I test the connection I get an Error like:

  "Initialisation of the provider failed. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

From what I could find, there are many possible reasons for this error, and I don't know if can get a more specific error message somehow.

With the Microsoft OLE DB Provider for ODBC Drivers and the ODBC-datasource for the Server the test connection worked.

In Microsoft Excel (32-Bit) I also tried both the first mentioned Connection String and the Connection String, which is built when entering the infos in the .udl settings. Same Error with both cases.

For my next test I used a .vbs - script, which was presented in the IBM Informix Developers Handbook:

On Error Resume Next

set conn=createobject("ADODB.Connection")

conn.connectionstring = "Provider=ifxoledbc;DataSource=mydatabase@myserver;UserID=myuser;Password=mypassword;"

conn.open

If Err Then
   WScript.Echo "Error!! Open"+conn.Errors(0).Description
Else
   WScript.Echo "Connected"
   conn.close
end if

This results once again results in the "multi-step"-Error.

The last thing I tried was using the Rowset Viewer of MDAC. I used the exact same input as with the .udl - File. But to my surprise it managed to connect! (It returned the error "Unknown Interface", but still worked) It correctly read the entries I set with the setnet32-Tool. (I checked this by temporarily removing one of the Entries, which resulted in an additional error and no connection)

Now I have a few questions:

Is there anything additional to configure on the Client PC?
How does the Viewer succeed, where every other test fails?
Is there any way to get a more accurate error code?
Are there major flaws in my tackling of the problem?

I appreciate every suggestion anybody can give me.


texts:
I run a daily backup of my database through command prompt:

c:\sqlcmd -H localhost -Q "BACKUP DATABASE test TO DISK='c:\test.bak'"

But the new backup replaces the previous backup. Please tell me what to change so that all backups will be stored on disk.


texts: Search for first and lastname without order

texts:
maybe there is a simple answer to my problem:

I have a mssql table ('Person') with a field for first and one for last name.
I am coding a Program, where the User should be able to search for persons.

  SELECT id, firstname, lastname FROM Person WHERE firstname LIKE
  @searchstring + '%' OR lastname LIKE @searchstring + '%'

doesn't work very well, because if the user types 'John W' nothing is found.

For example the person I search for is called John Williams:
It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'

Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.

And the query should be as fast as possible, because it is executed at every keydown...

edit: forgot to mention that the database must not be changed, because it is from another company.

Thank you for your help.


texts: How to query results from a huge postgres database?

texts:
I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly!

Even to find unique rows of a column it takes more than 10 minutes. Either I use a command line or tool like PHPMyAdmin to fire the query the wait time remains the same.

For example:

SELECT COUNT (DISTINCT column_name) FROM table_name;

I am worried that if use joins operations on these tables this wait time will increase significantly and will be more painful.

Could anyone suggest what could be a possible solution to get the statistics back quickly?


texts: InnoDB, What would cause: "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

texts:
MySQL 5.5.24 .
We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on.
The tables were freshly dumped and restored just before that.
Suddenly I noticed this error in error.log a few weeks ago, it has not reocurred:

"Error "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

Here is the 'show create table';

*************************** 1. row ***************************
       Table: tableABC
Create Table: CREATE TABLE `tableABC` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  `IMPORT_PAGE_NUMBER` int(11) NOT NULL DEFAULT '0',
  `MODEL_INTEGRATION_IMPORT_INSTANCE_UID` bigint(20) NOT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID` bigint(20) DEFAULT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_UID` bigint(20) DEFAULT NULL,
  `FIELD_TYPE` int(11) NOT NULL,
  `TICKER_CODE` varchar(12) NOT NULL,
  `FIELD_CODE` varchar(255) NOT NULL,
  `ACCOUNT_NAME` varchar(255) DEFAULT NULL,
  `IMPORT_VALUE` longtext NOT NULL,
  `VALUE_DATE` datetime DEFAULT NULL,
  `IS_HISTORIC` tinyint(4) NOT NULL DEFAULT '1',
  `PERIOD_CODE` varchar(4) DEFAULT NULL,
  `YEAR` char(4) DEFAULT NULL,
  `CONSOLIDATION_PRINCIPLE` varchar(255) DEFAULT 'NA',
  `UNIT_MULTIPLIER` double DEFAULT NULL,
  `CURRENCY_ISO` varchar(3) DEFAULT NULL,
  `LINE_NUMBER` bigint(20) DEFAULT '0',
  `COLUMN_NUMBER` bigint(20) DEFAULT '0',
  `PERIOD_START_DATE` datetime DEFAULT NULL,
  `PERIOD_END_DATE` datetime DEFAULT NULL,
  `PARSE_ERROR` varchar(255) DEFAULT NULL,
  `VALIDATE_ERROR` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),
  CONSTRAINT `FK_MIIInstanceData_MIIInstance` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`) REFERENCES `ModelIntegrationImportInstance` (`UID`),
  CONSTRAINT `FK_miii_data_miif` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_UID`) REFERENCES `ModelIntegrationField` (`UID`),
  CONSTRAINT `FK_miii_data_miif_mapping` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`) REFERENCES `ModelIntegrationImportFieldMapping` (`UID`)
) ENGINE=InnoDB AUTO_INCREMENT=83483107 DEFAULT CHARSET=latin1

Any ideas?
Do I need to rebuild the indexes? Would running mysqlcheck -a (analyze table) or mysqlcheck -o (optimize/alter table on innodb) or both do this?


texts: SQL 2005- stored procedure performance troubleshooting

texts:
I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:

Analyze the stored procedure for any bad TSQL practices (like count * etc)
Run it through DTA
Study the stored procedure's estimated query plan
Find the cached plan by running this code in the database:

SELECT  deqp.dbid ,
    deqp.objectid ,
    deqp.encrypted ,
    deqp.query_plan
FROM sys.dm_exec_query_stats deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE objectid = OBJECT_ID('procedurename', 'p') ;

Find the related indexes (not sure how) and check the usage
Try to find any missing indexes

How else should I do it?
Comments are really appreciated.

Thanks in advance


texts: Concurrent transactions result in race condition with unique constraint on insert

texts:
I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation and create the records in the database.

I found a bug today when two http requests had been made within the same second which caused this function to be called with identical data twice. There is a clause inside the function which makes a select on a table to see if a value exists, if it does exist then I take the ID and use that on my next operation, if it doesn't then I insert the data, get back the ID and then use that on the next operation. Below is a simple example.

select id into articleId from articles where title = 'my new blog';
if articleId is null then
    insert into articles (title, content) values (_title, _content)
    returning id into articleId;
end if;
-- Continue, using articleId to represent the article for next operations...

As you can probably guess, I got a phantom read on the data where both transactions entered the if articleId is null then block and tried to insert onto the table. One succeeded and the other blew up because of a unique constraint on a field.

I've had a look around at how to defend against this and found a few different options but none seem like they fit our needs for a few reasons and I'm struggling to find any alternatives.

insert ... on conflict do nothing/update... I first looked at the on conflict option which looked good however the only option is to do nothing which then doesn't return the ID of the record that caused the collision, and do update won't work as it will cause triggers to be fired off when in reality the data hasn't changed. In some instances this isn't a problem but in many cases this might invalidate sessions user sessions which isn't something we can do.
set transaction isolation level serializable; this seems like the most attractive answer, however even our test suite can cause read/write dependencies where, like the above, we want to insert if something doesn't exist and return it if it does and carry on with further operations. If we have several transactions pending that run the above code it will cause a read/write dependency error as outlined in the transaction-iso of the Postgres docs.

How should is this sort of concurrent read/write transaction handled?

Neither myself or my team claim to be database experts, let alone Postgres experts but feel like this must be a solved problem, or one people have come across in the past. We're open to any suggestions. If the information provided above is not enough, please comment and I'll add more information as needed.


texts: Joining of two tables, one containing a large amount of data. Need help optimising the query

texts:
I have been given a job of taking two tables (trimmed down create scripts below) and merging their data into a third table.
CREATE TABLE dbo.Ztest_nominal
(
  BMCode       varchar(20)     NOT NULL,
  nomcode      varchar(500)    NOT NULL,
  Description  nvarchar(4000)  NULL,
  LanguageID   int             NOT NULL,
  CONSTRAINT PK_Ztest_nominal PRIMARY KEY CLUSTERED (BMCode ASC)
)

CREATE TABLE dbo.Ztest_Participation
(
  ParticipationID       int IDENTITY(1,1)  NOT NULL,
  BMCode                varchar(20)        NOT NULL,
  LatestVersionNo       varchar(5)         NOT NULL,
  LastVersionSubmitted  varchar(5)         NOT NULL,
  CONSTRAINT PK_Ztest_Participation PRIMARY KEY CLUSTERED (BMCode ASC)
)

The third tables columns to take the contents of above (basically the above two tables):
[ParticipationID] [int] IDENTITY(1,1) NOT NULL,
[BMCode] [varchar](20) NOT NULL,
[LatestVersionNo] [varchar](5) NOT NULL,
[LastVersionSubmitted] [varchar](5) NOT NULL,
[nomcode] [varchar](500) NOT NULL,
[Description] [nvarchar](4000) NULL,
[LanguageID] [int] NOT NULL

Ztest_nominal has about 63,000,000 rows and Ztest_Participation about 62,000
The sql I was going to use (with an insert into) is (I did have the inner select outside before I gave up and posted here!):
SELECT
  p.ParticipationId,
  p.LatestVersionNo,
  n.*
FROM
  Ztest_nominal n
INNER JOIN
  (
    SELECT
      ParticipationId,
      LatestVersionNo,
      BMCode
    FROM
      Ztest_participation
    WHERE
      LatestVersionNo = LastVersionSubmitted
  ) p
    ON p.BMCode = n.BMCode

However i feel its going need some optimisation so its not slow, also I've already had out of memory errors when running it.
Any help would be appreciated


texts: Efficient Provision of Multiple Versions of Data

texts:
We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows).  The datasets are used by potentially several hundred different research projects, and each project needs its own version of the data, with certain columns of the data encrypted to a project-specific value, as well as other restrictions (like only providing certain rows and/or columns that a project has permission to us).

Here is a simplified example of some of the data:

Hospital_spell

persion_id   spell_id  hospital_id   admission_dt   discharge_dt
1234         9023      12            2012-09-04     2012-09-05
1234         1111      12            2014-01-01     2014-01-01
9876         5432      8             1999-04-27     2000-02-29

Hospital_diagnosis

spell_id  hospital_id  diag_num diag_code
9023      12           1        A12
9023      12           2        C45

These two tables link on spell_id and hospital_id.  For each project that uses this data, we need to provide a version of the data with person_id, spell_id, and hospital_id encrypted to new values (this restriction limits unauthorised combination of data from different projects).  Also, a given project might only get data from certain years or for certain people (however many projects get the entire dataset).

How can we provide these multiple versions of the data efficiently, in terms of performance and storage?  Ideally this would mean not copying all of the data for each project, and having only a limited performance hit for each project's use of the data (30% slower than querying the original tables would be fine, but not 300% slower).

Options we have considered:

Making a copy of the data for each project. This results in no performance hit, but when each project has potentially billions of rows, it takes up a lot of storage space with mostly duplicated data.
A view of the original table that encrypts on the fly with a scalar function.  Uses the least storage space, but performance is not acceptable, particularly when the encrypted columns are often used for joins (as above).
A view of the original table that joins to an additional table with precomputed encrypted values.  This has been our preferred option, but it turns out to also perform quite poorly.  Queries against these views are many times slower than against the original table.  We have worked on optimising indexing, but it hasn't solved this.

Example of the third option:

Additional table:

person_id_encrypted

person_id   person_id_e
1234        1A30495810293D0293CB2093A3FED2B

View:

create view hospital_spell_encrypted as (
    select person_id_e,
           spell_id_e,
           hospital_id_e,
           admission_dt,
           discharge_dt
      from hospital_spell
      join person_id_encrypted on
         hospital_spell.person_id = person_id_encrypted.person_id
      join spell_id_encrypted on
         hospital_spell.spell_id = spell_id_encrypted.spell_id
      join hospital_id_encrypted on
         hospital_spell.hospital_id = hospital_id_encrypted.hospital_id
)

Does anyone have any thoughts on how we can get better performance?  So far, just copying the data for each project looks like the only workable solution.

We are using DB2 9.7 LUW, running on an IBM Blue C Supercomputer with multiple parallel servers.


texts: 2016 Read Only Secondaries With Different Indexes

texts:
The other day, someone told me that one feature in SQL Server 2016 involving read-only secondaries for reporting was that the read-only secondary could have different indexes than the primary.  I've searched for this everywhere and haven't found this to be the case.  In fact, Microsoft seems to indicate the opposite here (in this manner, indexing is similar to 2012).

Just curious if I'm reading this correctly; read-only secondaries with different indexing would provide an option for reporting, but I don't see them being compatible with AG groups in any version of SQL Server.


texts: MongoDB Replication initial sync fails with OplogStartMissing

texts:
I'm currently testing replication in our development environment.

Our current (primary) mongo instance has around 70-80 GB of data. I created a new instance and added it to the replica set.

It takes around 4 hours to sync the collections at which point it starts playing back the oplog to catch up with writes on the primary. But doing rs.printSlaveReplicationInfo() shows that the secondary keeps lagging more and more behind the master, and eventually fails with the following error:

OplogStartMissing: error fetching oplog during initial sync :: caused by :: Our last optime fetched: { ts: Timestamp(1581379449, 5879), t: 4 }. source's GTE: { ts: Timestamp(1581379449, 20317), t: 4 }

At which point the initial sync starts over again. Both the master and slave are using version 4.2


texts: Can you have a WHERE clause with a greater than operator on a composite key?

texts:
I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :

CREATE TABLE `bbcmapimp_pc_raw` (
  `postcode` char(8) NOT NULL,
  `pc_sector` char(6) DEFAULT NULL,
  `pc_district` char(4) DEFAULT NULL,
  `pc_area` char(2) DEFAULT NULL,
  `parliamentary_constituency_code` char(9) DEFAULT NULL,
  `parliamentary_constituency` varchar(255) DEFAULT NULL,
  `mp_name` varchar(255) DEFAULT NULL,
  `district_code` char(4) DEFAULT NULL,
  `district` varchar(45) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `suffix` char(5) DEFAULT NULL,
  PRIMARY KEY (`postcode`),
  KEY `pcc` (`parliamentary_constituency_code`),
  KEY `suffix_index` (`pc_area`,`suffix`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I just need to iterate the table, but in my first attempt I used OFFSET which was horrendous! I've since learned what MySQL was doing under the bonnet, and understood why it was getting slower and slower.

So, I'm trying to modify my query to use a WHERE clause instead, but the issue is that I'm ordering the query by two fields (the table has a composite index defined).

One solution would be to add an ID field that's in the correct order and use that; but I was wondering if there was any way I could specify both fields of the composite index in a single greater than WHERE clause?


texts: How can I improve this DB query? it takes too much time

texts:
The query is this:

$today = $date->getTimestamp();

SELECT t.*, u.screen_name, u.profile_image_url
  FROM table AS t
  LEFT JOIN users AS u
  ON t.user_id=u.id_str
  WHERE UNIX_TIMESTAMP(t.created_at) > {$today}
  ORDER BY id DESC
  LIMIT 12;


texts: Do SQL Server Transaction Logs get 'locked' when there is a non-zero log_reuse_wait?

texts:
Scenario:

I have an application attempting to write to a SQL Server DB, and I'm getting the message:

  The transaction log for database 'DB_NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

However, I see that there is plenty of space on the drive for the transaction log to be written and autogrowth is on.  So from SQL Management Studio, I issue:

select name,
    log_reuse_wait,
    log_reuse_wait_desc
  from sys.databases
  where name = 'DB_NAME'

and can see that the log_reuse_wait is '3'.

 Question:

Do SQL Server transaction logs get 'locked' in any way when this wait is in place?

Note that this is set to 'Simple' recover model.  Please let me know if there is more information I need to provide to make this a complete question.


texts: I need a trigger that updates a table row field by checking date that is stored in their own field without any interaction of user. i.e I am working on a project where I have to book rooms for user for a particular date and time and at that case I want to show room status as "booked" but after expiry of that date and time our database automatically update room status "booked" to "available". means I want that database keep track of their record and update it automatically. How I can achieve this?


texts: SQL Server freezes (because of application), need logging

texts:
We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unannounced) will cause the SQL Server to freeze. I can't even restart the SQL Server service; I have to restart the entire machine.

Needless to say, I can't open a query window to run sp_who2 to find the cause. It could be days before the issue shows up again. Is there any type of logging I can put in place to better track down what is causing the SQL Server to freeze up?

exec xp_readerrorlog only shows me what has happened after the restart so it isn't much help.

At the time it freezes, CPU is pegged at 90-97% and memory is maxed at 8 GB. Server has 12 GB but the max is set to 8192 for SQL Server.


texts: SQL TFS Visual Studio Solution: One Database or Multiple Databases

texts:
When creating a TFS Visual Studio 2017 solution with SQL Server 2016, should the solution contain

one database project per solution, or
multiple database projects in a single solution?

Our system has databases which are interlinked/cooperate in creating one service.
I believe the correct answer is "multiple databases in one TFS Visual Studio solution"; please validate.

Thanks,


texts: Suddenly Slow Execution Plan for Stored Proc

texts:
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, and two dates.

Now depending on the dates, and the customer, this query can return anything from zero to 1000s of rows.

The problem: what we've experienced is that suddenly we will get a number of errors (typically Execution Timeout Expired or similar) for a particular client while they try execute that stored proc. So we examine the query, run it in SSMS and and find that it takes 30s. So we recompile the stored proc and -bang- it runs now in 300ms.

I've spoken to our DBA about this. He has told me that the database created a query plan when we created the stored proc. He said that it was a good plan for that set of parameters, but if you throw a certain set of parameters at it, then the plan will not be the best plan for that data, and so you will see it running slow.

The options presented to me are the move that problem query from a stored proc and back into dynamic SQL that has it's execution plan created on every run.

This feels like a step back to me and I feel like there must be a way around this. Is there any other way to deal with this issue?

Any and all responses are appreciated.


texts: Postgres 9.0 recovery from inadvertent drop-all-tables event

texts:
Due to a accidental config file check-in, a unit test wiped out our entire production database (did a drop, then re-created the tables).  Trying to restore from backup revealed the recent backups were corrupted and the only valid backup we have is just over 1 year old.

What are my options for recovering the data (if any exist)?  Autovacuum may have been turned on. I saw a tool pg_dirtyread, but it appears to require Postgres 9.1 or higher, and from what I can tell does not help with drop/create table scenarios.