texts:
I am using libpqxx to communicate with a PostgreSQL 9.3 server.

For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each.  If they are all wrapped up into a single CTE, the entire prepared statement costs approximately 2ms.

In fact, nearly all individual prepared statements cost approximately 2ms, 1ms for unlogged tables.

What explains this exactly?

Edit

I first broke down my most expensive set of 14 prepared statements into two CTEs to avoid the single row write rule.

This dropped the average time consumed from below 40ms to approximately 10ms.

To avoid the single row write rule, I tried a function with input parameters.  This was a disaster despite its convenience, driving time consumed to approximately 500ms.

I just completed the single CTE, and I have found no difference between using two CTEs in halves and the single, but I think there may be some caveats.

To merge the two CTEs, I had to work a variant of this solution because of how I'm moving data between tables, copying from one table to another, deleting that source data, and rebuilding it.

Keys that are not present in the new data are deleted, coexisting ones are updated if they have new data, and keys not present in the old data are inserted, converting a delete+insert into a delete+update+insert.

This prepared CTE is now almost 25 queries long vs the original 14; however, very few rows are being moved.  In the future, many more rows should be moved.

It seems strange that the single CTE would consume as much time as the two-CTE since all queries are running more or less concurrently, and less rows are being altered.  My guess is that since this CTE is much larger, it's taking more trials for the server to come up with an optimal query plan.

Single CTE now only consumes 5ms on average

As I ran the query more, the average started dropping.  It's currently dropped to a 5ms average.  This is on an i7 laptop. I still haven't ran the query that many times, so there may be room for more improvement.

Since I have no knowledge of the PostgreSQL source code and can't determine how much of the performance was due to roundtrips between database and application despite them being on the same box, my best guess is that nearly all of the performance increase is due to the efficiency of PostgreSQL CTEs, considering the default concurrent execution of the sub-statements and main query.

Hopefully, an expert can give a full explanation, but as for me and for performance, I will try to cram every process into a single CTE.


texts: Trigger to restrict users from creating a new table in the database

texts:
How do I create a SQL Server trigger that will restrict any users from creating a new table in the database?

Please help me with this.

Thanks & Regards,
Ritesh.


texts:
How can I issue commands through SQL*Plus on Windows?

In Linux is like this:

sqlplus -s /nolog<<EOF
conn username/password@database
EXECUTE some_proc;
EXIT;
EOF

And Windows?


texts: How to resolve the error for "Alter Subscription disable"?

texts:
I have the following subscription for logical replication:

pctest1=> SELECT subscription_name, status FROM pglogical.show_subscription_status();
          subscription_name          |   status
-------------------------------------+-------------
         sandbox_rds_pg10_subscriber | replicating
(1 row)

When I try to disable this, it returns the below error:

pctest1=>  alter SUBSCRIPTION sandbox_rds_pg10_subscriber disable;
ERROR:  subscription "sandbox_rds_pg10_subscriber" does not exist


texts: DELETE query against a linked server issues error

texts:
I have a query that is part of a bigger process, and runs against a linked server.

The code is like this:

DELETE FROM [LinkServerIP].[Database].[dbo].[TableName]
WHERE NOT EXISTS (
    SELECT *
    FROM [LinkServerIP].[Database].[dbo].[AnotherTableName] t
    WHERE t.[Code]=[LinkServerIP].[Database].[dbo].[TableName].[Code]
);

Now, when I run this I get:

  Msg 4104, Level 16, State 1, Procedure xxxx, Line 113 The multi-part identifier "LinkServerIP.Database.dbo.TableName.Code" could not be bound.

Any ideas?


texts: SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid

texts:
I am trying to install and start db2 on my linux machine using the following script:

#!/usr/bin/env bash

MKDIR=/bin/mkdir
RM=/bin/rm
TAR=/bin/tar

DB2_SERVER_PROGRAM_PATH=/home/alok/Documents/Installers
DB2_SERVER_PROGRAM=v10.5_linuxx64_expc.tar.gz

echo "changing to $DB_SERVER_PROGRAM_PATH"
cd ${DB2_SERVER_PROGRAM_PATH}
echo "removing expc"
${RM} -rf expc
echo "untarring ${DB2_SERVER_PROGRAM}"
${TAR} zxf ${DB2_SERVER_PROGRAM}

echo "changing to expc"
cd expc

echo "Installing db2 as root"
sudo ./db2_install

echo "Adding user group db2grp1 as root"
sudo groupadd db2grp1
echo "Adding user group dasadm1 as root"
sudo groupadd dasadm1
echo "Adding user group db2fgrp1 as root"
sudo groupadd db2fgrp1

echo "Adding user db2inst1. Please provide password for new user db2inst1."
sudo useradd -g db2grp1 -G dasadm1 -m db2inst1
sudo passwd db2inst1

echo "Adding user dasusr1. Please provide password for new user dasusr1."
sudo useradd -g dasadm1 -G db2grp1 -m dasusr1
sudo passwd dasusr1

echo "Adding user db2fenc1. Please provide password for new user db2fenc1."
sudo useradd -g db2fgrp1 -m db2fenc1
sudo passwd db2fenc1

cd /opt/ibm/db2/V10.5/instance

echo "Creating DAS as root via user dasusr1."
sudo ./dascrt -u dasusr1

echo "Creating database instance as root via user db2fenc1 and db2inst1."
sudo ./db2icrt -u db2fenc1 db2inst1

echo "Setting communication protocol to tcpip as user db2inst1."
sudo -u db2inst1 ../adm/db2set DB2COMM=tcpip
echo "Setting SVCENAME 50000 for dbm cfg as user db2inst1."
sudo -u db2inst1 ../bin/db2 update dbm cfg using SVCENAME 50000

echo "starting db2 as user db2inst1."
sudo chmod +x ../adm/db2start
sudo -u db2inst1 ../adm/db2start

netstat -an | grep 50000

Everything works fine, till I get to the penultimate line:

sudo -u db2inst1 ../adm/db2start

SQL1641N  The db2start command failed because one or more DB2 database
manager program files was prevented from executing with root
privileges by file system mount settings.

Now I checked /etc/fstab and it says:

proc            /proc           proc    nodev,noexec,nosuid 0       0
/dev/sda1       /               ext4    errors=remount-ro,user_xattr 0       1
UUID=d4d794eb-1e68-4ddf-a9ec-249a22f084fb none            swap    sw             0       0
/swapfile swap swap defaults 0 0

My installation path, /opt/ibm/db2/V10.5/adm is basically on /dev/sda1 and it's not mounted using the nosuid option.

Here is the output of mount:

alok@hostname:~$ mount
/dev/sda1 on / type ext4 (rw,errors=remount-ro,user_xattr)
proc on /proc type proc (rw,noexec,nosuid,nodev)
sysfs on /sys type sysfs (rw,noexec,nosuid,nodev)
none on /sys/fs/fuse/connections type fusectl (rw)
none on /sys/kernel/debug type debugfs (rw)
none on /sys/kernel/security type securityfs (rw)
udev on /dev type devtmpfs (rw,mode=0755)
devpts on /dev/pts type devpts (rw,noexec,nosuid,gid=5,mode=0620)
tmpfs on /run type tmpfs (rw,noexec,nosuid,size=10%,mode=0755)
none on /run/lock type tmpfs (rw,noexec,nosuid,nodev,size=5242880)
none on /run/shm type tmpfs (rw,nosuid,nodev)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,noexec,nosuid,nodev)
shmfs on /dev/shm type tmpfs (rw,size=2048m)
gvfs-fuse-daemon on /home/alok/.gvfs type fuse.gvfs-fuse-daemon (rw,nosuid,nodev,user=alok)

This again indicates that / is not mounted in nosuid mode.
So why am I getting error SQL1641N? Is it a different problem throwing out this message instead?


texts: Too many columns, how to reduce it? User events, ratings, scores storing for each lesson

texts:
I am working on a eLearning project. It has:

Courses

Courses (n courses)
-Modules (at least 3 modules in one course = n x 3)
--Lessons (at least 8 lessons in one module = n x 8 x 3 = n x 24 items)
---Quizzes (at least 5 quizzes in one lesson = n x 24 x 5 = n x 120 items)
---Training (1 training per course = n x 24 x 1 = n x 24 items)

I need to give rating for each lesson overall score (1, 2 or 3 stars rating)
I need to record users quizzes data (wrong answers, right answers etc.)

The table will grow too large if I were to store values in individual lessons as columns and quiz itmes as columns. Let's say we have 5000 users, 500 quiz items, 100 lessons.  It means, there are 500 columns across 5000 rows.

Help me reducing the columns and make the databased optimised by avoiding NULL entries.


texts: pt-table-sync error: Called not_in_left in state 0

texts:
I have setup a Mysql replication between 2 servers, using Percona Xtrabackup:

Master is a MySQL 5.0.91 Community Edition (CentOS 4.8)

Slave is a MySQL 5.1.68 Community Edition (CentOS 6.4)

When starting the slave, some replication queries where blocked because of some unknown "temp" tables.
I used a few SQL_SLAVE_SKIP_COUNTER commands to hide the problem. And now that the replication is up to date, I try to resync the tables.
=> 2 tables are out of sync. I use pt-table-sync to resync.

The first table has been resynced without any problem (a few UPDATE to replay)

But the second table, a huge table (57GB), give me this error after some time (varying from a few minutes to a few hours):

pt-table-sync --verbose --execute -uroot -p h=10.2.0.1,D=MYDB,t=MyTable h=10.2.0.2

# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Called not_in_left in state 0 at /usr/bin/pt-table-sync line 5500.  while doing MYDB.MyTable on 10.2.0.2
#      0       0      0      0 0         16:38:24 19:08:17 1    MYDB.MyTable

Note that I launch pt-table-sync from a third server on the local network.

I don't find much information about this error.
What would you recommand to help me solve this problem?


texts: Issue of structure of query does not match function result type

texts:
"structure of query does not match function result type" this isssue occurred when i try to execute the this function.

CREATE OR REPLACE FUNCTION historicalavg01() RETURNS SETOF text AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);

        IF(a = 27) THEN
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();

Please give the solution for it. thanks in adavance.


texts: MongoDB: operator to match arrays whose elements are all contained in a specified array

texts:
The $all operator selects the documents where the value of a field is an array that contains all the specified elements.

In my situation I need the opposite: an operator that selects the documents where the value of a field is an array whose elements are all contained in the specified array.

For example, consider the following collection:

{"name": 1, "vals": ["A", "B"]}
{"name": 2, "vals": ["D", "C"]}
{"name": 3, "vals": ["A", "D"]}

I want to select all the documents whose vals field contains values in ["A", "B", "D"]; that is, documents 1 and 3 (not 2 because "C" is not contained in the specified array).

I cannot use {vals: {$all: ["A", "B", "D"]}}, nor {vals: ["A", "B", "D"]} as both of these operations select the documents whose vals field contains all the specified values, which is not what I want.

Is what I need possible?


texts: Is DB mail supported on SQL Server hosted on Azure VM (IaaS)?

texts:
Is DB mail present in SQL Server hosted on Azure VM (IaaS)?

If DB mail is not supported, then what are the features available?


texts:
I work in Oracle SQL Developer and use Oracle 11g express edition.

Tables, that are invoked here look like:

CREATE TABLE orders
(
    id_order             INTEGER NOT NULL ,
    total                NUMBER(10,2) NOT NULL ,
    id_user              NUMBER NOT NULL ,
    purchase_date        DATE NOT NULL ,
    id_delivery          NUMBER NOT NULL ,
    id_payment           NUMBER NOT NULL ,
    id_status            NUMBER NOT NULL ,
    distance             NUMBER(5) NULL
);

CREATE TABLE order_item
(
    id_item              NUMBER NOT NULL ,
    id_order             NUMBER NOT NULL ,
    id_watch             NUMBER NOT NULL ,
    quantity             NUMBER NOT NULL
);

CREATE TABLE users
(
    id_user              NUMBER NOT NULL ,
    login                VARCHAR2(100) UNIQUE NOT NULL ,
    pass                 VARCHAR2(100) NOT NULL ,
    email                VARCHAR2(200) NOT NULL ,
    full_name            VARCHAR2(200),
    birth_date           DATE ,
    balance              NUMBER(15,6),
    id_role              NUMBER NOT NULL ,
    discount             NUMBER
);

CREATE TABLE watches
(
    id_watch             NUMBER NOT NULL ,
    id_brand             NUMBER NOT NULL ,
    id_mechanism         NUMBER NOT NULL ,
    id_bracelet          NUMBER NOT NULL ,
    id_gender            NUMBER NOT NULL ,
    cost                 NUMBER(8,2) NOT NULL ,
    model                VARCHAR2(100) NOT NULL ,
    description          VARCHAR2(2000) NOT NULL ,
    image_path           VARCHAR2(100) NOT NULL ,
    id_factory_country   NUMBER NOT NULL ,
    id_stock             NUMBER NULL
);

When I try to compile next code:

CREATE OR REPLACE TRIGGER addItemInOrder AFTER INSERT ON order_item
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;

I get a message box like this:

This seems that :NEW is treated as bindable variable, but when I omit semicolon I get another error:

  Error(4,25): PL/SQL: ORA-00904: "NEW"."ID_ORDER": invalid identifier

That is sad as well.

So I have 2 questions:

1) Is it correct to use :NEW in AFTER INSERT trigger in WHERE clause?

2) Why everything compiles and works fine when I execute SET DEFINE OFF; before DDL statement?

And of course, thanks for your answers)


texts: Select statement on tables with the same name and schema but in different databases

texts:
I have a table that lists the names of the databases that have tables with the same schema. Each customer has a different database.

Is there a way to run a select on same table in multiple databases?

The first query I wrote was to get a list of databases concatenated the table name.

Select concat(database_name,'.','TableA') from dbnamelist

This results in output like

database1.TableA
database2.TableA

Now I want to select a value in the column type but across different tables.

Select type from database1.TableA,database2,TableA

Is it possible to do this in a single SQL statement?


texts: How often to backup database and what are the favoroble practices to do so

texts:
Curious to know on how often you guys back up SaaS?

I have determine that i need backups to be done every hour for an application I have, based on how often data is updated an amount of users. I have estimated that in an hour i can have about 10 new entries and about 100-200 updated entries, something on those lines, some times, at peak, it could be 2-3 times more than that.

How do you determine this number and what do you guys do?


texts: SQL Server giving intermittant connection failures

texts:
I have a SQL Server 2000 running on Windows 2003 Server that is giving me intermittent connection failures.

The user connections are getting closed on waiting but mature connections.  Meaning when users are connected to an application that is running on this server, the programs start up fine.  The problem usually shows up after the connection has been sitting for a bit.  I'm wondering if there is some odd setting that is killing older, not active connections?

What could be causing this?  What can I check to further troubleshoot this.

EDIT:

Is it possible that my problems are caused by running SQL Server 2000 personal edition, where the old server that died and was replaced was on Standard?  I have no way to verify the old version, but is this possible?

EDIT: Well I upgraded the edition to SQL 2000 Standard, and there is nothing different in the behavior of the server.  So that wasn't it.


texts: What is the "Default Timeout" value in SQLite connection string used for?

texts:
I have looked at the SQLite documentation and I can't find anything clear about the value "Default Timeout".

Looking at other websites, it seems to refer to some sort of "Connection timeout", while other sites indicate that it is more of a "Command timeout"

Some quick tests don't seem to indicate that it has anything to do with Commands.

Can anybody confirm/reference what the purpose of that value in the connection string is for?

Edit this is using the official SQLite .NET library


texts:
I have a dump file using expdp with compression (oracle version >= 11g).
Before running impdp I want to check if there is enough space on the machine but it seems as there is no parameter in the impdp utility to assists me (ESTIMATE=BLOCKS doesn't work)

What are my possibilities?


texts:
As per subject when a sequence hits its max val what happens?

What is the behavior? I have a big table with a sequence as a primary key and would like to know.

Thanks.


texts:
I have a ~300 million row table with a column that has 9 different values. I need to be able to SELECT INTO 9 different new tables based on this column.

At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a CASE statement that defines the table to go into, or something similar?

I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO CASE
        WHEN col1=N'tbl1' THEN [dbo].[tbl1]
        WHEN col1=N'tbl2' THEN [dbo].[tbl2]
    END
FROM [dbo][tblBase]

There is no clustered index on the base table. There is an index on col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.

The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).


texts: Recommended Data profiling tool for BI developer

texts: What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.


texts: Recursively call STUFF for results in a table valued function

texts:
This is a static query that kind of shows where I am trying to get

declare @Airbill VARCHAR(50) = '12345678912'
select stuff(stuff(@Airbill, 4, 0, '-'), 9, 0, '-')

But I need to make it more variable.  I have the following varible:

DECLARE @AirbillMask VARCHAR(50) = '999-9999-9999'

Which can (and does) change.  It is always '9's and '-'s.  But the placement of them changes.

If @AirbillMask is 999-99-99-9999 my desired result would be 123-45-67-8912.
If @AirbillMask is  999-9999-9999 my desired result would be 123-4567-8912.

I am running dbo.FindPatternLocation(@AirbillMask, '-') that returns a table of dash locations.

I was planning to find a way to STUFF the parts of the @Airbill into it corresponding locations to produce a calculated field of FormattedAirbill, but I got stuck trying to iterate down into the STUFF calls.

Is there a way to call STUFF inside another STUFF call, over and over till I am out of rows?

I feel like a recursive CTE or cross apply could accomplish this, but I can't seem to wrap my head around it.

NOTE: I would prefer to do this without looping, if possible.


texts: Log Custom SQL Results During Maintenance Plan

texts:
I would like the last step of my Maintenance Plan to execute a simple custom SQL script (SELECT * FROM <SomeTable> WHERE <Condition>), and save the results to a file (Text, CSV, etc).

Is this possible? The closest thing I've found is doing this in a .bat file that is executed by Windows, but that seems a little cumbersome.


texts: How can I install the SQL Server PowerShell module on an offline machine?

texts:
I have some SQL Server machines that cannot connect to any external sites.

I have been using PowerShell more and more when managing databases, so I definitely need this module to be installed so that I can use my PowerShell routines.

It has been proven difficult to install the SQL Server module on those offline machines.

When I try to install the module:

the SqlServer module asks for the NuGet module package:

I then get this ocean of red letters and the following error message as you can see on the picture below:

  WARNING: Unable to download from URI
  'https://go.microsoft.com/fwlink/?LinkID=627338&clcid=0x409' to ''.

  WARNING: Unable to download the list of available providers. Check
  your internet connection. PackageManagement\Get-PackageProvider :
  Unable to find package provider 'NuGet'.

  It may not be imported yet.
  Try 'Get-PackageProvider -ListAvailable'.

  At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7415
  char:30
  + ... tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet ...

What is the workaround for this PowerShell module installation?


texts:
My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class for the android app.

I need to select a temperature row for each hour interval offset by 30 minutes (so 1:30-2:30) by taking the most recent to the hour reading. Here's what I've tried so far using a database of lat/lons that I have. I used avg() here because it may be easier to get a picture of the weather around that hour by averaging temperature reading within that interval. I've been using SQLite Studio to get a hang of writing queries in SQL:

select avg(longitude), time_stamp, strftime('%H',time_stamp)
from events
group by strftime('%H', time_stamp) + round((time_stamp - 1800)/3600)

And my result:

-81.4919071241071   2015-11-11 11:21:53 11
-48.8079752772727   2015-11-11 12:59:31 12
-54.104869527907    2015-11-11 13:44:16 13
-88.0822953625      2015-11-11 14:37:38 14

It looks okay BUT there's data it's not including from 11:30-12:30, even though it's definitely present in the database. The first row is fine, the second row should go with 13, 3rd row with hour 14, and 4th row with hour 15.

Sample data with columns: row, event_id, type, machine_id, longitude, lat, time_stamp (datetime), other_machine, synched

42  1   2   -89.4813131 40.7400818  2015-11-11 11:27:30 3   0
43  9   1   -89.4813908 40.7401891  2015-11-11 11:21:53 -1  0
44  8   1             0          0  2015-11-11 11:27:52 -1  0
45  0   2   -89.4812174 40.7401841  2015-11-11 11:27:55 3   0
46  4   2   -89.4813087 40.7401857  2015-11-11 11:29:10 -1  0
47  5   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
48  4   2   -89.4813087 40.7401857  2015-11-11 11:29:13 -1  0
49  5   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
50  4   2   -89.4812153 40.7401323  2015-11-11 11:30:02 -1  0
51  2   2   -89.4812291 40.7400435  2015-11-11 11:30:20 3   0
52  5   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0
53  4   2   -89.4812718 40.7401197  2015-11-11 11:30:46 -1  0

This sample data shows where half of the points would group with hour 11 and the ones after 11:30 would go with 12.


texts: SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement?

texts:
I am using Schema_Object_access_Group audit action type. When I run a select, insert, update SQL statement, it records all statements nicely.

For example, captured Sql statement looks like :

UPDATE TOP (200) SomeTable
SET ColName1 = @ColName1
WHERE (ColName2 = @ColName2) AND ...

It is not explaining what was updated value and which row was updated.

I want to capture updated actual value used in SET ColName1 = @ColName1, is there an audit action type to accomplish the job. I want a way to capture parameters used in any select, update, insert statements.

Willing to use audit events as change log, to track who changed what without using the following:

Trigger approach to log all changed values
Change Data Capture "CDC"


texts: How can I improve query performances using Using temporary; Using filesort

texts:
I am very new to sql and query optimization, I am sorry if its inappropriate to ask such as may be asked previously. But I need help and some thoughts where I can look and start ...

EXPLAIN
SELECT DISTINCT node.nid AS nid,
                node.title AS node_title,
                votingapi_cache_node_points_VotingNumber_sum.value AS votingapi_cache_node_points_votingnumber_sum_value,
                node.created AS node_created,
                votingapi_cache_node_hot_VotingNumber_reddit.value AS votingapi_cache_node_hot_votingnumber_reddit_value,
                'node' AS field_data_field_url_node_entity_type,
                'node' AS field_data_field_images_node_entity_type,
                'node' AS field_data_title_field_node_entity_type,
                'node' AS field_data_field_topic2_node_entity_type
FROM node node
LEFT JOIN votingapi_cache votingapi_cache_node_hot_VotingNumber_reddit
    ON  node.nid = votingapi_cache_node_hot_VotingNumber_reddit.entity_id
    AND (votingapi_cache_node_hot_VotingNumber_reddit.entity_type = 'node'
    AND votingapi_cache_node_hot_VotingNumber_reddit.value_type = 'hot'
    AND votingapi_cache_node_hot_VotingNumber_reddit.tag = 'VotingNumber'
    AND votingapi_cache_node_hot_VotingNumber_reddit.function = 'reddit')
LEFT JOIN votingapi_cache votingapi_cache_node_points_VotingNumber_sum
    ON  node.nid = votingapi_cache_node_points_VotingNumber_sum.entity_id
    AND (votingapi_cache_node_points_VotingNumber_sum.entity_type = 'node'
    AND votingapi_cache_node_points_VotingNumber_sum.value_type = 'points'
    AND votingapi_cache_node_points_VotingNumber_sum.tag = 'VotingNumber'
    AND votingapi_cache_node_points_VotingNumber_sum.function = 'sum')
INNER JOIN taxonomy_index taxonomy_index
    ON  node.nid = taxonomy_index.nid
LEFT JOIN field_data_title_field field_data_title_field
    ON node.nid = field_data_title_field.entity_id
    AND (field_data_title_field.entity_type = 'node'
    AND field_data_title_field.deleted = '0')
WHERE ((    (node.status = '1')
        AND (node.type IN  ('link'))
        AND (taxonomy_index.tid = '1')
        AND (field_data_title_field.language IN  ('sv'))
        AND (votingapi_cache_node_points_VotingNumber_sum.value >= '1')
      ))
ORDER BY votingapi_cache_node_hot_votingnumber_reddit_value DESC
LIMIT 10 OFFSET 0

Explain shows like below image ie: using Using temporary; Using filesort etc

Please suggest !
Thanks!


texts: what does a "/" mean after a sql statement

texts:
I have a script that we are using to update our ERP database to a new version,

UPDATE ENUM_CODES SET COLUMN_NAME = 'PAY_STATUS' WHERE TABLE_NAME = 'PAYABLE' and COLUMN_NAME = 'STATUS'
/

but i dont know why it has the / after the update portion.
Does anyone know what that / means?


texts: Oracle Kerberos LDAP authentication without KTPASS

texts:
I am working on trying to set up LDAP authentication on an Oracle 19c machine. The Active directory server I am authenticating against is a government service and they don't allow the kind of passwordless logins that are required in these instructions I found.  They will grant me a service account to bind to, but the name is generated and uneditable in any way, also it needs to be reset every 60 days, cuz rules.
Given these constraints, is there any way that I can set Oracle up to authenticate users against that system?


texts: how to run limit query within condition in mysql

texts:
I have a huge database around ~20,000,000, presently I am running a php script, which verifies if user info is 'valid or invalid'. There are several entries named 'invalid' in the database. Problem is how to make a limit on database to scan only valid entries. I am running google cloud to device messaging service which only allows push messages to 200,000 users per day. So, I want to make limit per query and automate via cron.

Presently query which is running on database is

for valid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='valid' ORDER BY id;

for invalid:

SELECT * FROM TNAME WHERE SETNO=1 & flag='invalid' ORDER BY id;

Different numbers are assigned to setno corresponding to database rows.


texts: How to automate the detection of the column type of a csv file for a table creation script?

texts:
I am new to pgAdmin and Azure database. I have a huge csv file with around 220 columns and I want to create a table out of it in pgAdmin4 to push it to Azure.

However I don't know how to detect automatically the types of columns.

Here is an exemple :

IDI GKID    S01Q01  S02Q01_Gender ...
100093  enq030059569748fc89091fdd91cc337cac44eca90  Yes, I agree    Female ...

I'm doing the script to create the table. However, given the number of columns I would like to automate it to get the script that would allow me to add the csv to the database in pgAdmin4 for Microsoft Azure.

Then, after transposing the csv header, I get:

    IDI
    GKID
    S01Q01
    S02Q01_Gender
    ...

I'm doing the script to create the table. However, given the number of columns I would like, in the best case, to automate the detection of the type of column to be able to write it in the right part, in the worst case to put a generic type of the type TEXT.

So far, I've tried

output = ""
file_name = "columns.txt"
string_to_add = " TINYTEXT,"

with open(file_name, 'r') as f:
    file_lines = [''.join([x.strip(), string_to_add, '\n']) for x in f.readlines()]

with open(file_name, 'w') as f:
    f.writelines(file_lines)

It gives me back:

IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

And, then, I can do:

CREATE TABLE my_table (
IDI TINYTEXT,
GKID TINYTEXT,
S01Q01 TINYTEXT,
S02Q01_Gender TINYTEXT,
...

But I'm not sure that this enough to make a table able to receive my csv file.


texts: MySQL will not run after exhausting disk space while indexing

texts:
After I tried to index a column in a huge table (40G) and ~5million rows, I can no longer connect to the datatbase.

Indexing failed with this error

Later, I found out I ran out of disk space while mysql was stopped. And, now I can not get mysql run again. I tried almost everything on SO except where purging mysql/reinstalling was mentioned. Is there anything I can do to get my data back or run mysql again? Or, I could extract my tables from ibdata1?


texts:
Table1

Id Name  DemoID
1  a      33
2  b      44
3  c      33
4  d      33
5  e      44

Table2

Id DemoID IsTrue
11  33     1
12  44     1

Now we can find which DemoID is present in Table2 using below 2 queries using join and using sub-query, but which is more accurate/relevant for more number of records?

using join -

SELECT Table1.Id FROM Table1  Table1 join Table2 Table2 on Table1.DemoID  = Table2.DemoID
WHERE Table2.IsTrue = 1

using sub-query -

SELECT Table1.Id  FROM Table1  Table1
WHERE Table1.DemoID  in (select Table2.DemoID from Table2 Table2)


texts: Is there benefit from having 2 tables for 1 entity?

texts:
I'm using mysql 5.7
I'm making app that has 2 activities: list of entities and viewing single entity (full).

The full entity has 7 related entities and 100 columns.

On the list activity I need only 5 columns and 2 related entities.

I'm thinking if I make separate table for the listing (5 columns + 2 related entities) denormalized. And then when searching through normalized columns get only ids. And get all deneromalized stuf with single where id in 1,5,7 manner.

Would it singificantly increase performance or should i don't bother?

I'm making an android app and want the best speed i can get but dunno if it really matters


texts:
We had an issue in where a query would hang indefinitely with the inclusion of certain IDs in the last WHERE IN statement here:

SELECT 1
FROM items
WHERE id NOT IN (
                 SELECT items_id
                 FROM "items_attribute"
                 WHERE "items_attribute"."attribute_id" IN
                       (88376, 88354, 88380, 74984, 12983, 1205, 10931, 78061)
);

Those IDs that caused the hanging did not seem to present with any data anomaly. Do you see any issues with this query that would cause recursion or any potential issues? We seemed to resolve the issue by making the subselect DISTINCT. The select in question returns 164760 rows. There are no views here, only small indexed tables.

Explain:

Seq Scan on items (cost=2594.08..3816558214.87 rows=164034 width=0)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=2594.08..25511.56 rows=139699 width=4)
           ->  Bitmap Heap Scan on items_attribute  (cost=2594.08..24267.06 rows=139699 width=4)
                 Recheck Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))
                 ->  Bitmap Index Scan on items_attribute_attribute_id_index  (cost=0.00..2559.16 rows=139699 width=0)
                       Index Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))

Thank you so much for taking a look!!


texts: Replicating tables from LAN database to web server?

texts:
I have an in-house database (that's actually part of a third-party product) running on 2008R2 Express. We'd like to display some of the data on a new website we're working on (running 2008R2). I'm more developer than administrator, so I'm wondering the best method for getting data from one to the other. Data doesn't have to be available real-time, daily replication is acceptable.

Again, this is display only, so two-way replication is not necessary.

I know of replication as a concept and have heard of Sync Framework, but have no experience with either and don't know where to start. I also don't have access to install anything on either server, so to my understanding this rules out Sync Framework.

I've been reading up on replication, but when I looked into creating a publication on the LAN database, I didn't have the option to create one (I'm assuming this is because it's Express). I've also looked into mirroring and snapshots, but I'm not sure where to start.

I'm able to create a linked server from the website database to the LAN database, but for security and performance reasons I can't query directly, which is why I need to replicate the data.


texts:
Recently, I was given a task to print all the prime numbers (1-100). I failed drastically there. My Code:

Create Procedure PrintPrimeNumbers
@startnum int,
@endnum int
AS
BEGIN
Declare @a INT;
Declare @i INT = 1
(
Select a = @startnum / 2;
WHILE @i<@a
BEGIN
@startnum%(@a-@i)
i=i+1;
)
END

Though I ended up without completing it, I wonder is it feasible to do such programs on Database (SQL Server 2008 R2).

If yes, how it may end.


texts: Changing Column type to avoid Arithmetic overflow error

texts:
I have a table that saves user hits.  It has these columns:

ID , UserID , HitDate , Userlocation , UserUniqueID

Today I'm getting this error "Arithmetic overflow error". After I checked it out I find my Last hit ID was "2147483647" and also my Column type is

Int type , Primary Key with AutoNumber enabled

So the only solution is to Alter the column to bigint type (I guess). I've tried this code :

Alter HitTable alter column id bigint

but it returns this error :

Msg 5074 level 6 stat 1 line 1

The Object 'PK_HitTable' is dependent on colun 'id'

Alter Table Alter Column id failed because one or more objects access this column

So how can I change this huge table without losing any data? Will droping the current constraint cause data loss?  Is below code safe and will it give me the result (also with autonumber)?

ALTER TABLE HitTable
DROP CONSTRAINT PK_HitTable
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable
ADD CONSTRAINT HitTable PRIMARY KEY CLUSTERED (ID);

Or should I use this?

ALTER TABLE HitTable NOCHECK CONSTRAINT ALL
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable NOCHECK CONSTRAINT ALL

Also I should mention this table is used heavily.


texts: Dropped column still has value when recreated with Postgres table of 150M rows

texts:
I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working:

ALTER TABLE "public"."WorkoutExercises" DROP COLUMN "_etl";
ALTER TABLE "public"."WorkoutExercises" ADD COLUMN "_etl" bool;

However

SELECT
    *
FROM
    "WorkoutExercises"
WHERE
    "_etl" = TRUE
LIMIT 1000;

Returns 1000 results. Why is that, and how can this be fixed?


texts: SQL Server compact- Query that return 2 constant columns even if query didn't return data

texts:
Please look at the following simple query:

Select CustomerID
From Customers c
Where  c.CustomerId = "1234"

I want to write similar query that does the following:

Always return 2 columns with constant string instead of CustomerID in this structure:

If value was found then return 2 columns:
Found Value in DB, CustomerId

Else return:
Didn't found value in DB, CustomerId

How can I do that? Especially in Microsoft compact SQL server limited syntax...


texts:
I'm trying to grant permissions to the current user logged in depending on their role, but it doesn't seem possible to have a select statement at the end of the GRANT PROXY # TO # command

The code I have for this is

CASE (SELECT RoleType FROM user)
WHEN 'standard' THEN
    GRANT PROXY ON 'standard_user'@'localhost' TO (SELECT CURRENT_USER())
WHEN 'admin' THEN
    GRANT PROXY ON 'admin_user'@'localhost' TO (SELECT 'Username' FROM user)@'%'
END CASE;

Does anyone have another idea on how I can achieve this?


texts: Joining a table on an id but need to copy this data to other ids


texts: Does sqlite preserve COLLATE on tables in a view?

texts: In sqlite3 collations are defined per-column. If you CREATE TEMPORARY TABLE foo AS SELECT ... the resulting temporary table foo does not inherit any custom collations from the columns in the SELECT.  What happens if you CREATE VIEW foo AS SELECT ...? Does the view "table" keep column collations or lose them?


texts: Is it possible to perform skip N rows and fetch M rows without full scan

texts:
Let's say I've got this query:

SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
   WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME;

Now if I want to show only the first ten rows between 50 and 60, the only way I can think of is to first run the above query with ROWNUM pseudocolumn and then select from the result of this query. Something like this:

SELECT EMP_ID,LAST_NAME FROM
 (SELECT  ROWNUM RNUM,EMP_ID,LAST_NAME FROM
  (SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
     WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME))
  WHERE RNUM BETWEEN 50 AND 60;

But this way, I'll end up first fetching all the employees whose positions are 1 or 3 and then extract the 10 rows among them. I find this a bit inefficient. Is there any way to achieve this without making full scan first?


texts: I've have configured the resource governor for cpu, memory, and IO.  I have my dbcc db checks running under a pool and workload that only allows 10% of memory and cpu, and only 100 IOPS.  I have also been tracking it as it happens and it looks like it is working just fine.  Limiting things the way I would expect.  However, it seems to be affecting other pools and workloads at the IO level.  Each db mdf and ldf sit on a netapp san, on their own volume.  There doesn't seem to be any io contention on the netapp side.  I wouldn't expect the IO governor to affect other things like this.  What should I be looking at to fix this?  The wait type on the dbcc is pagelatch_sh.  I also see plenty of pagelatch_up and _ex for the other jobs that end up taking longer than normal.


texts: Where is the reference to explain the SSMS Object Explorer icons?

texts:
(This is not the same as the two questions asking about a blue circle with white question mark).

The Object Explorer in SQL Server Management Studio can show many different kinds of icons. Where on earth is the documentation to explain them? (Even the other questions on here about the blue circle and white question mark don't point to any references from Microsoft, and various answers simply give various ways to "get rid of" the icon and the only answer that explains the meaning says "I think it means..." This is very poor on Microsoft's part. Why not provide context help in a pop-up when you hover over the icons?)

Below is a variety of icons I can find without trying too hard.

To put the image words into parsable text:

green circle with a white triangle facing right (eg. database engine)
green circle with a white triangle facing right, preceded by a black x (eg. sql server agent)
green circle with a white triangle facing right, preceded by a black arrow pointing right (eg. distributed transactions coordinator, under management)
red x at top right (data collection, under management)
red x preceded by a black cube (resource governor, under management)
black square with lower half filled black and upper half white (policy management, under management)
two blue arrows, one facing left, the other facing right (availability group)
two blue arrows forming a circle (availability group replica)
red circle with white square, preceded by a black x (sql server agent, with service disabled)
red x (agent job, disabled)

I mean some of these I can guess - but where on earth is the full list of possible icons documented?

(It's the two blue arrows forming a circle on the availability group replica that got me searching just now).


texts: Create database level constants (enumerations) without using CLR?

texts:
I have a several SQL objects that need to take alternate actions based on a desired state of the request.  Is there a way to create database level constants (enumerations) that can be passed to stored procedures, table-valued functions, and used in queries (without using CLR)?

CREATE PROCEDURE dbo.DoSomeWork(@param1 INTEGER, ..., @EnumValue myEnumType)  AS ...;

and then use it:

EXEC doSomeWork 85, ..., (myEnumType.EnumValue1 + myEnumType.EnumValue2);

Where myEnumType would hold a few enumeration values.

In the procedure I would be able to use @EnumValue and test it against values in myEnumType to do the required work. I would make the values of myEnumType a bitmask for the case I am considering.

For a simple example, consider an expensive process that takes a huge dataset and reduces it to a smaller but still very large dataset. In this process you need to make some adjustment in the middle of that process that will affect the result. Say this is filter for (or against) some types of records based on some status of an intermediate calculation within the reduction. The @EnumValue of type myEnumType could be used to test for this

SELECT   ...
FROM     ...
WHERE       (@EnumValue & myEnumType.EnumValue1 = myEnumType.EnumValue1 AND ...)
        OR  (@EnumValue & myEnumType.EnumValue2 = myEnumType.EnumValue2 AND ...)
        OR  ...

Are these sort of database level constants possible in SQL Server without the use of CLR?

I am seeking a database level enumeration or set of constants that can be passed as parameters to stored procedures, functions, and so on.


texts: What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement?

texts:
2M rows inserted using LOAD DATA INFILE to innodb takes 7.5min, and profiling shows 99% of that time is in "System lock".

  Does this tell me anything useful?

   mysql> set profiling=1;
   Query OK, 0 rows affected (0.00 sec)

   mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
   Query OK, 1964807 rows affected, 8 warnings (7 min 27.35 sec)
   Records: 1964806  Deleted: 1  Skipped: 0  Warnings: 8

   mysql> show profile for query 1;
   +------------------------------+------------+
   | Status                       | Duration   |
   +------------------------------+------------+
   | starting                     |   0.000206 |
   | checking permissions         |   0.000015 |
   | Opening tables               |   0.000034 |
   | System lock                  | 447.327523 |
   | Waiting for query cache lock |   0.000352 |
   | query end                    |   0.000011 |
   | closing tables               |   0.000014 |
   | freeing items                |   0.000033 |
   | logging slow query           |   0.000007 |
   | logging slow query           |   0.000006 |
   | cleaning up                  |   0.000006 |
   +------------------------------+------------+
   11 rows in set (0.02 sec)


texts: postgres finding child tables for particular table

texts:
I want to find all child tables corresponding to particular parent table.

SELECT *
FROM information_schema.table_constraints tc
  right JOIN information_schema.constraint_column_usage ccu
          ON tc.constraint_catalog=ccu.constraint_catalog
         AND tc.constraint_schema = ccu.constraint_schema
         AND tc.constraint_name = ccu.constraint_name
         and ccu.table_name in ('plugin')
 WHERE lower(tc.constraint_type) in ('foreign key');

Now this is working fine in public schema. but when I use 'a.plugin' instead of 'plugin' where a is my schema then it is not working. (i.e it is not working inside particular schema when I try to use table inside schema as a parent table).


texts:
I am trying to load data into mysql database form text file.

LOAD DATA LOCAL INFILE 'P:\file.txt' INTO TABLE table_january
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

  error code: 1148. The used command is note allowed with this MySQL

MySQL version 8.0.19.


texts: Decomposition of a relation to 2NF then to 3NF

texts:
I'm currently studying for my exams and the trouble I am having is how to decompose a relation R with given functional dependencies into 2NF then 3NF.

For example for the following R and functional dependencies:

R = {A, B, C, D, E, F, G, H, I, J}

Functional dependencies F = ( {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F}
-> {G, H}, {D} -> {I,J} }

I know firstly you have to work out the closure to find the key for R, which I have done and the key is {A,B} and now this is where I get stuck. My text book doesn't give any examples on how one can solve this except for definitions of 2NF and 3NF.

Example on how I can do this will be greatly appreciated.


texts: Statistics update with automatic sampling messes up density vector and histogram

texts:
I have a table that contains 22 millions of records.

I have noticed that statistics for a column were way off, even if that column has a constant distribution: in fact, every value is repeated twice.

To help you visualize this scenario, think of a table with check-in and check-out dates (2 distinct records) for every unique id of another table.

These are the statistics with a full scan:

These are the statistics with automatic sampling:

Why this weird behaviour? Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary? Should I create a statistics update job with fullscan for some statistics? And if that's the case, how do I know which are the statistics that need this kind of treatment?

Additional informations:

There is no foreign key constraint on that column
The column is a numeric(14,0) NULL


texts:
There are 3 new server roles in SQL Server 2016:

mediumrc
largerc
xlargerc

Any idea what these are for? Google doesn't give any information about these.


texts: MS Migration Assistant (SSMA) index renamed

texts:
I am using Microsoft SQL Server Migration Assistant v6 (aka SSMA) to update a database from Access to SQL Server.  I am getting informational messages that say "A2SS0029: Index name 'Name' was changed during conversion.".

As these are just index names, that shouldn't cause a problem for me.  But I do wonder why the name has to be changed.  Particularrly since I was able to create an index with the given name manually after the migration.

Why are the index names being renamed? And am I wrong about that not causing any problems (given that I don't have any queries that use index hints, if that is even possible with Access).


texts: Are non 'copy-only' full backups safe to recover from?

texts:
I've recently realized the difference between a full backup and a 'Copy-only' full backup and I'm wondering if the backups I'm taking on a weekly and daily basis are safe to recover from.

This article suggests that differential backups can only be traced back to a 'parent' (if that is the correct word) full backup. To me this sounds like a DB with FULL recovery mode allows for a point-in-time recovery to the last full backup?

In any case. I'm currently taking a full backup on a daily basis and no differential backups. Some of the databases being backed up have a recovery mode of simple, some full and some are bulked-logged. However, there is no need to do point in time restore on any of them at this point (These databases are updated once a day via vendor dumps). I'm also not taking any transaction log backups of the full-recovery mode databases for now (because I don't know how to do this automatically yet I'm watching them manually until they start growing faster than they currently do).

Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken, regardless of the recovery model and regardless if there is a log backup or not?


texts: MySQL: What points should we consider when create composite index

texts:
There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key.
Beside this, we create composite index on the basis of our SQL query requirements. Ordering of column in a composite index plays a vital role in the selection of right index in query execution which improves the performance.
If a composite index has only 2 columns then its easy to determine their order.

But, how we will decide the ordering of columns if we have many
columns and many different SQL queries.
What are the other points which should be focused when create composite index?
What is the role of cardinality and modality of a column in creating index and composite indices?


texts: Insert Statement Take too long to insert - MYSQL

texts:
Following Insert query taking too long time to insert :

  Query_time: 14.798782  Lock_time: 0.000062 Rows_sent: 0  Rows_examined: 0

SET timestamp=1523459374;
insert into patient_last_examined
     (patient_id,operator_id,section_name,created_date,status,save_or_review)
VALUES  ('227350','72','Allergies','2018-04-11 11:09:20','0','1');

Table have 17k records before insertion and
Total Column : 13
Index Columns : id (PK), patient_id,section_name
Db Engine : Inno DB

Can you please suggest how to increase the performance?
(At timestamp=1523459374, 15-20 more insertion take place for other tables)


texts: A workaround for restoring HierarchyId in SQL server 2014

texts:
After installing VS 2015 CTP 4, HierarchyID stopped working in SQL Server 2014. This is the bug listed here.
I am looking for a workaround.
To that end, I have tried reinstalling everything associated with SQL Server 2014 and Visual Studio 2015 CTP.
Additionally, I noticed the following (before the reinstall):

After re-installing, I now have version 12.0.2000.8 installed, and its 6.80 MB.
Additionally, I know that I have a working version of the SQL Server type assembly as I can run the following C# code without error:
        var root = SqlHierarchyId.Parse("/");
        var parent = SqlHierarchyId.Parse("/1/");
        var child = SqlHierarchyId.Parse("/1/1/");
        Console.WriteLine(child.ToString());

While in SQL, the following errors:
    declare @Root hierarchyid = '/',
    @Parent hierarchyid = '/1/',
    @Child hierarchyid = '/1/1/';

    print @Root.ToString();
    print @Parent.ToString();
    print @Child.ToString(); -- Throws exception

Is there a way to force SQL Server 2014 to use a particular assembly for this type? Additionally, if I could track down the actual source of the file that is the source of the error I would have a chance at restoring that file/set of files. Is there a way to get the error message to show the file that it originated from?


texts: When I run "start tuning" by db2 data studio it gives me the below message. This still happens when I run it under user "db2inst", the default user which I configured when I installed db2 on RedHat 7.2


texts: best practice either create multiple databases or multiple users

texts:
I have several application which currently running on my local. I have installed oracle 11g and create a global database name as oracle11. Now should i create again different database for each application using Database configuration assistance or should i create different users for each application under that global database? What would be the best practice? If i create different users its also behave same as creating different databases right?

What if in a production environment where server house for couple of application? Do i need to do the same as my local by creating one database and different users for each application?


texts: Unable to create a datasource. Datasource fails to 'test successfully' for SSRS

texts:
I imported an SSRS 2008 Datasource to SSRS 2012 using RS Scripter tool.

The DB connection is the same for the SSRS 2008 and SSRS 2012 data source created.

I can 'test successfully' the login/pw for SSRS 2008 datasource, but  'fail' with SSRS 2012.

The login has permissions and exists (They are the same for both SSRS datasources)

Any suggestions on how to trouble shoot this?
I asked Server Admins if there are firewall issues, they stated there shouldn't be any.
I was able to ping both hosts (though maybe that doesn't mean much sometimes)


texts: what happens to user connections when an AG fails over?

texts:
I'm not sure quite how to word this, but I am looking for anything a DBA can do (things outside of changing code in the application) to reduce the impact to users when an AG fails over.

We have a 2 node AG running SQL 2016 SP2 CU10 in synchronous commit mode, connecting with a listener.

What controls the switching of connections to the new primary node?  is there a way to accelerate the switch?  Anything in the connection string, AG settings, changing timeouts, etc. that would help make the switch faster for users?


texts: What permission do I need for viewing... all current user permissions?

texts:
I'm maintaining an application that runs with SQL Server 8.0.760 SP3.

When I try to "see" the permissions of the current user I'm logged with, I do this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

However, the server returns this error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_my_permissions'.

If I try the following query: SELECT * FROM dbo.fn_my_permissions(NULL, 'SERVER');, I get the same error message.

How can I list all the current user permissions?


texts: Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

texts:
I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database.

The query I've always used (see below) has served me well until we began creating multiple data files per filegroup and I'm only able to figure out how to get as granular as the filegroup level.

select
    SchemaName = sh.name,
    TableName = t.name,
    IndexName = i.name,
    PartitionNumber = p.partition_number,
    IndexID = i.index_id,
    IndexDataspaceID = i.data_space_id,
    AllocUnitDataspaceID = au.data_space_id,
    PartitionRows = p.rows
from sys.allocation_units au
join sys.partitions p
    on au.container_id = p.partition_id
join sys.indexes i
    on i.object_id = p.object_id
    and i.index_id = p.index_id
join sys.tables t
    on p.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where sh.name != 'sys'
    and au.type = 2
union all
select
    sh.name,
    t.name,
    i.name,
    p.partition_number,
    i.index_id,
    i.data_space_id,
    au.data_space_id,
    p.rows
from sys.allocation_units au
join sys.partitions p
    on au.container_id = p.hobt_id
join sys.indexes i
    on i.object_id = p.object_id
    and i.index_id = p.index_id
join sys.tables t
    on p.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where sh.name != 'sys'
    and au.type in (1,3)
order by t.name, i.index_id,p.partition_number;

However, this query won't work when there are multiple files in a filegroup as I can only get so far as to relate an allocation unit to a data space and, ultimately, a filegroup. I'd like to know if there's another DMV or catalog that I'm missing that I can use to further identify which file in the filegroup contains an allocation unit.

The question behind this question is that I'm trying to evaluate the actual effects of compressing partitioned structures. I know I can do a before-and-after using FILEPROPERTY(FileName,'SpaceUsed') for the file and a before-and-after on sys.allocation_units.used_pages/128. to get this information, but the exercise itself made me wonder if I could identify the specific file that contains a specific allocation unit.

I've been messing around with %%physloc%% in the hopes it could help, but it doesn't quite get me what I'm seeking. The links below were provided by Aaron Bertrand:

Where are my Rows? – Using the %%physloc%% Virtual Column (sqlity.net)
SQL Server 2008: New (undocumented) physical row locator function by Paul Randal


texts: Index Fragmentation - Am I interpreting the results correctly?

texts:
I'm not a DBA, but I'm responsible for a database that currently has hundreds of tables and ~5TB of data. I recently ran the following query, in hopes of determining index fragmentation:

Declare @DatabaseId Int = DB_ID('ODS')

SELECT
    OBJECT_NAME(T.OBJECT_ID) as TableName,
    T2.Name as IndexName,
    T.index_id as IndexId,
    index_type_desc as IndexType,
    index_level as IndexLevel,
    avg_fragmentation_in_percent as AverageFragmentationPercent,
    avg_page_space_used_in_percent as AveragePageSpaceUsedPercent,
    page_count as PageCount
FROM sys.dm_db_index_physical_stats (@DatabaseId, NULL, NULL, NULL, 'DETAILED') T
    INNER JOIN [sys].[indexes] T2 ON T.index_id = T2.index_id And T.object_id = T2.object_id
ORDER BY avg_fragmentation_in_percent DESC

The first 30-40 rows of the result set looks as follows (after importing into Excel):

This was very startling to me. Am I reading this correctly, that I have all these indexes, actually a lot more, that are 100% fragmented? Is my query correct?


texts: How can I find the shortest path between two currencies?

texts:
I have table currency_pair(c1, c2); values are (usd,bnb), (cake,bnb), (cake,eth).
I need to find the shortest path that allows me to compare usd to eth.
The result here would be those same values. I can then use the first pair to establish a usd-bnb relationship, which I can then use to calculate a usd-cake relationship, which I can then use to compute usd-eth.
Since order is not determinate, the first step I did was create a materialized view, currency_pair_map(c1, c2), which is a union of select c1, c2 union select c2, c1. This would seem to simplify the logic.
If I am thinking about this correctly, what I need to do is use WITH RECURSIVE? I should also have some sort "depth_limit" parameter that ensures that a query fails if it is impossible to establish a pair.
Thinking about this out loud, we should always begin with:
SELECT *
FROM currency_pair
WHERE
  c1 = 'usd' AND
  c2 = 'eth'

If there is a result, we should end there.
If there is not, then we need to find all usd-* pairs and continue search until we find one that ends with eth.
Using this logic, so far I have:

WITH RECURSIVE pair_route AS (
  SELECT
    1 depth,
    cp1.id,
    cp1.c1,
    cp1.c2
  FROM currency_pair cp1
  WHERE
    cp1.c1 = 'usd'
  UNION
  SELECT
    pr1.depth + 1,
    cp2.id,
    cp2.c1,
    cp2.c2
  FROM pair_route pr1
  INNER JOIN currency_pair cp2 ON cp2.c1 = pr1.c2
  WHERE
    pr1.depth < 4
)
SELECT *
FROM pair_route;

I think this is correct. Now I just need to track path (IDs) and identify the shortest paths.


texts:
I have two questions for you guys. I really appreciate your help.

1.- Is there a way to rename an Availability Group?

2.- Is there a way to get the T-SQL statements in order to re create a Availability Group in case that I need to drop it and create it again?

If there's information that you want me to share, please let me know and I will provide it.


texts: How do I tell what agent profile is being used?

texts:
When you change from one replication agent profile to another using the SSMS GUI, you often have to stop and restart the agent for the change to take effect.

I'm trying to debug an issue where it looks like someone changed the active profile, but replication is behaving as if a different profile was being used. How do I determine the actual replication agent profile being used?

A parallel - when using sp_configure, both the configured values and the run values are returned. The Replication GUI seems to be returning the configured values. How do I determine the running values for the agent profile?

We have a profile that is set to skip certain errors. We turn on that profile briefly, only if needed. And then we turn the default profile back on. It looks like someone turned on the error skipping profile and left it on. But when I open the agent profile dialog box, it's set to the correct, default profile.


texts: How to set up Amazon RDS parameter group for Postgres?

texts: I'm trying to create a parameter group in Amazon Web Services for an Postgres 9.5 RDS instance. I'm a little confused by the units being used for things such as shared_buffers and min_wal_size. Typically these would be specified in postgresql.conf with units, like 4GB or 128kB. If I enter the same in AWS I get an error that the value I specified was invalid, but strictly numeric values seem to be fine. Does anyone know what units these parameters are expecting?


texts: Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

texts:
My database log is growing out of control, I recently removed the database from an availability group by when I look at the log reuse wait description it shows "AVAILABILITY_REPLICA"

I have tried to set the database to simple mode and then shrink the log file, I have tried full backups, log backups (in full mode after a full backup). But the status will not change and I cannot shrink the log file.

Any suggestions?


texts: SQL Server : database recovery from snapshot files

texts:
We have completely lost our SQL Server 2000 database in an accident. The only thing left is a snapshot folder which contains bunch of .sch, .idx and .bcp files.

The sch and idx files have a clear text format and holding the database schema.

The .bcp files looks to be binaries encoded somehow.

Is there any way to recover the data from the .bcp files?


texts: SQL Agent - PSexec remote commands causing SSIS packages to hang

texts:
First post here, so please be kind!

I have a bit of a problem with a set of SQL Agent jobs (SQL Server 2014, running on Windows server 2012 R2) that I am trying to setup.

Each job is a multiple step job running as follows:

SSIS Step
SQL Command (checks the load of the SSIS)
System Command (runs a remote batch file via PSExec)
SQL command (checks the status of the prior batch)

The Batch job that we are running via PSExec can take up to an hour to complete.  Also, we have not choice but to run the batch file remotely.

What is happening is while the PSExec command is running, all other SSIS steps in all other jobs hang (even those not related apart from the fact that they are SSIS jobs).   As soon as the PSExec step completes, the SSIS jobs start.

Has anyone experienced this before?  Does anyone have a work around that we could implement?


texts: Dropping One Computed Column Causes All Computed Columns To No Longer Replicate

texts:
We recently dropped a computed column from a table that was participating in merge replication.  After issuing the statement the other two computed columns remained in the table but stopped replicating out to the subscribers.

This became apparent while building a new subscriber I noticed that these other computed columns were missing and causing the snapshot to not apply since one of the views required these columns.

Checking the article list I could see those columns were available but the check box next to "publish" was missing on the selector. When I tried to add it manually it said I'd have to reinitialize every subscriber. Didn't want to do that as reinitializing takes a couple hours per client and there are over 30.

To get the columns to begin replicating again I decided to drop them ALL, and then add only the ones that I wanted.

ALTER Table Child DROP Column [Age]
ALTER Table Child ADD [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25)))
ALTER Table Child DROP Column [ChildFull]
ALTER Table Child ADD [ChildFull] AS (IsNull([First],'') + ' ' + IsNull([Last],''))

Once complete - the computed column for ChildFull was included in the list of replication articles but Age was not. So I ran the DDL statements again like this.

ALTER Table Child DROP Column [Age]
ALTER Table Child DROP Column [ChildFull]
ALTER Table Child ADD [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25)))
ALTER Table Child ADD [ChildFull] AS (IsNull([First],'') + ' ' + IsNull([Last],''))

After that everything was back and working no problem.

In three different attempts I was able to verify that dropping one computed column turns replication off for all other computed columns in the table.

I've posted this as a potential bug on Microsoft - let me know if anyone can offer reason as to why this is not a bug.


texts: Importing .dmp file into SQL developer: Do I need to import seperately for each table?

texts:
I have a large .dmp file which I would like to import into SQL developer. This contains all the data I want to import across several Schemas.

Do I need to split this up into each separate table in order to import it?

I ask as in this guide it suggests importing on a table by table basis.


texts:
I installed Oracle XE 11g on my Ubuntu in vmware fusion. The oracle installation is all went fine but I am not able to connect the oracle database using sqlplus. I am getting this exception:

$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed May 27 18:40:24 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 1
Additional information: 3309588
Additional information: 8
Process ID: 0
Session ID: 0 Serial number: 0

I followed the instructions as per this excellent blog http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/ and got everything installed but feels like I am missing something obvious.
My have setup Vmware fusion with 2GB of Memory.


texts: How to deal with finding nearby locations when given a zip code or city?

texts:
I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and populated it.

Now, what I'd like to do is given a zip code, I want to find all zip codes within 20 miles of the radius. Here's the structure of my geoname table:

CREATE TABLE public.geoname
(
  geoname_id integer NOT NULL,
  name text,
  ascii_name text,
  alternate_names text,
  latitude double precision,
  longitude double precision,
  fclass text,
  fcode text,
  country text,
  cc2 text,
  admin1 text,
  admin2 text,
  admin3 text,
  admin4 text,
  population bigint,
  elevation integer,
  gtopo30 integer,
  timezone text,
  mod_date date,
  the_geom geometry(Point,4326),
  CONSTRAINT geoname_id_pk PRIMARY KEY (geoname_id)
)

CREATE INDEX idx_geoname_admin1
  ON public.geoname
  USING btree
  (admin1 COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_ascii_name
  ON public.geoname
  USING btree
  (ascii_name COLLATE pg_catalog."default");

CREATE INDEX idx_geoname_the_geom
  ON public.geoname
  USING gist
  (the_geom);

Here's the query I am using (it never completes, takes forever) and the EXPLAIN output:

SELECT ascii_name
FROM geoname
WHERE
ST_distance(
ST_PointFromText('POINT(-122.67621 45.52345)', 4326),
the_geom) < 16093

"Seq Scan on geoname  (cost=0.00..2729028.01 rows=3179247 width=14)"
"  Filter:   (st_distance('0101000020E61000009ACE4E0647AB5EC0AC8BDB6800C34640'::geometry, the_geom) < 16093::double precision)"

I was thinking about using ST_DWithin, but from what I can tell it requires two locations.

This is more-or-less what I am trying to accomplish.


texts:
I have received this error while performing queries on a database (SQL Server 2012)

  msg 824, level 24, state 2, line 1 sql server detected a logical consistency-based i/o error. Incorrect page ID (expected page ID  : 88:89436 ; real page ID : 0:0)  It occurred during a read of page (88:89436) in database ID 49 at offset 0x0000002bab8000 in file 'C:\BackupData\Sales\S0086.NDF'.

Can anyone tell me how to fix this issue?


texts: Can I split lines into Elements with For XML?

texts:
In this example I'm cribbing from a previous question, My source table has a varchar(max) field that I want to split by line into individual elements with FOR XML as I query it. Given this data-

CREATE TABLE Example
(
    [ID] INT
  , [Body] VARCHAR(Max)
);

INSERT INTO Example ([ID], [Body])
VALUES
        (1, 'This is a test'),
        (2, 'This is another
         test with
         two line breaks'),
        (3, 'This is a test
         with one line break');

I want to produce this XML   -

<row>
    <ID>1</ID>
    <Body>
        <Line>This is a test</Line>
    </Body>
</row>
<row>
    <ID>2</ID>
    <Body>
        <Line>This is another</Line>
        <Line>test with</Line>
        <Line>two line breaks</Line>
    </Body>
</row>
<row>
    <ID>3</ID>
    <Body>
        <Line>This is a test</Line>
        <Line>with one line break</Line>
    </Body>
</row>

Can I do this with FOR XML alone, or do I need to get xslt involved?


texts: Confusion about HA , LOG backups and availability

texts:
So I started to work with HA and I'm on a new job, in a new company that has already HA implemented, but nobody can remember who created or if it's working.

1) Availability Groups are in sync with it's replicas by log files, right? So the primary replica sends information from the LOG files to the secondary.

2) With log shipping, I need a job to backup log from primary, copy it to another drive, send it to the netword, and then another job in the secondary replica to restore this log into the database. I dont need this naymore with Availability groups right? Primary replica already sends log data directly from the log file to the secondary replica. Or is this wrong?

3) I ran a query and I noticed that there aren't backups in this server ( primary) since 2017, and logs since 2018. Is this a problem for Availability groups? with no log backup files, there's no information to send to the replica. Or 2) is right already?


texts: need to correct this Query/Loop to find and start "new branch" in the output

texts:
given these tables:

PERSONS
person_id   fname   lname      info
1          LB       Johnson   "info..."
2          Bobby    Baker     (null)
3          Malcolm  Wallace   (null)
4          Billy    Estes     (null)
5          Clifton  Carter    (null)
6          Howard   Hunt      (null)
7          Frank    Sturgis   (null)

ASSOCS
p_id    a_id    p_to_a      a_to_p
1       2      employs     worked for
1       3      knows       knows
3       4      workedwith  worked with
4       6      knows       knows
5       3      brother to  sister-in-law to
6       7      wife of     husband of
7       3      knows       knows
5       1      test        test_

The output (from the Query and Loops below) is this:

Lyndon B Johnson 1
   employs Bobby Baker 2
   knows Malcolm Wallace 3
      worked with Billy Sol Estes 4
         knows E. Howard Hunt 6
            wife of Frank Sturgis 7
               knows Malcolm Wallace 3
Clifton Carter 5 »
   brother-in-law to Malcolm Wallace 3
   test Lyndon B Johnson 1

I can see that it "chainlinks" 1 » 3 » 4, and because of this it's continuing the chain and does not start a new branch where I need it to (denoted here with » »).

(I'd also love it if someone could help me include this "info" field that I've added. I've made several attempts in the Query and PHP - i'll note them in the code at the bottom):

Lyndon B Johnson **
"info..."
**
   employs Bobby Baker
   knows Malcolm Wallace
      worked with Billy Sol Estes
         knows Howard Hunt »
» Howard Hunt
   wife of Frank Sturgis
Clifton Carter
   brother to Malcolm Wallace
      test LB Johnson

This fantastic snippet/query works well - almost. What I need to do is either find a new way to define the associations between 2 persons in my ASSOCS table in such a way that a new branch is defined, or get this query and loop to do so in some fashion. It would probably take both, I'm thinking, but I'm not good with arrays or this depth of php. (I hope php is appropriate here - it seems to me to still be administration of a db.)

There are also my (commented) attempts at including the new "info" field.

$sql = "SELECT a.p_id, a.a_id,
    CONCAT(p1.fname,' ',p1.lname) as name1,
    p_to_a as association,
    CONCAT(p2.fname,' ',p2.lname) as name2,
    /*** i tried p3.info **/
        FROM assocs a
        INNER JOIN persons p1 ON p1.person_id = a.p_id
        INNER JOIN persons p2 ON p2.person_id = a.a_id
        /** INNER JOIN persons p3 ON p3.info = a.p_id **/
    ";

$data = [];
$res = $db->query($sql);
while (list($pid, $aid, $n1, $ass, $n2, /** $nf **/) = $res->fetch_row()) {
    if (!isset($data[$pid])) {
        $data[$pid] =  [ 'name' => $n1, /** 'info' => $nf, **/ 'assocs' => [] ];
    }
    $data[$pid]['assocs'][$aid] = ['name' => $n2, /** 'info' => $nf, **/, 'rel' => $ass];
}

$processed=[];

function listAssociates($pid, &$data, &$processed, $level)
{
    if (!isset($data[$pid])) {
        return;
    }
    if (in_array($pid, $processed)) return; // prevent circular references
    $processed[] = $pid;

    // this is where a branch starts
    if ($level==0) {
     echo "
{$data[$pid]['name']} »
";
    }
        $indent = ($level*25);
        $indent = ($indent+25)."px";
        foreach ($data[$pid]['assocs'] as $aid=>$adata) {
            echo "
{$adata['rel']} {$adata['name']}
\n";
            listAssociates($aid, $data, $processed, $level+1);
        }
   /** I tried using various unset() or reset() here, to no avail **/
}

listAssociates(1, $data, $processed, 0);
listAssociates(6, $data, $processed, 0);
listAssociates(5, $data, $processed, 0);

very grateful for anyone's help. I really have tried hard to do this on my own.


texts: Modeling a database for easy counting / reporting

texts:
I have an app where user is known (user_id) and he can do several actions (action_id).
Every time he makes an action I need to save the fact that he made it for reports/analytics. I guess it is similar to other analytic solutions and their db design.

Once I have the data, provided with a time window (minutes resolution) I need to count for each user (all or some) the number of times he did actions and which actions he did. (sum all data grouped by action_id).

Some assumptions:

The number of users are ~1000.
Action types are ~100.
Actions can happen 24/7.
The time windows can span from minutes to days and are random.
A time window can't go back more than 30 days.

I'm considering SQL, NoSQL and RRD to save the data.

I put RRD here because it's easy to implement the insert of the data into statds+graphite. I'm concerned if I take this approach, the querying (although provided by graphite) will not be indexed and will probably have to count all the data whenever I ask for a window/user (no indexing). Another problem is that when querying all the data, all users info will be needed, resulting in reading all the files concurrently which I'm not sure is a good thing.

SQL - Very easy implementation when inserting the data and querying. Easy to index, order and group by. However I'm not sure it's easy if I'm anticipating high traffic. Also, I'm not sure how effective is the count() of sql (haven't used SQL in the last few years) after group by. Can it offer parallel computation?

NoSQL - Is there a solution out there that is the right fit for this type of scenario (perhaps a Map/Reduce algorithm to fast generation of counts in a time window?)

Thanks for helping me model


texts: Connecting to Oracle Database with ODBC/Python

texts:
We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm having the world's most difficult time connecting to it.

I need to access it two ways:

ODBC - I would like to setup an ODBC connection in Windows 7, so that I can pull the information using Excel. I know how to setup the connection, but I cannot for the life of me manage to setup the Oracle Drivers correctly. All the links from documentation for this piece of software go to dead/changed pages. Apparently the Oracle website has changed since this documentation was written.
Python - From what I can tell, cx_Oracle seems to be the best module, but again I can't seem to set it up correctly. And again, most documentation refers to sections of the Oracle website that has since changed. I'm not sold on using cx_Oracle if someone has a better recommendation.

Thanks for looking over this, and I appreciate your help.


texts: MySQL replication: most important config parameters for performance on slave server?

texts:
I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever.

Since, if I understand correctly, slave only works on updates/inserts, what are the most critical configuration parameters that I can adjust in slave's my.cnf to ensure best performance and smallest possible latency?

Master: 32Gb RAM, Slave: 4Gb RAM
Mostly innodb tables
Percona-server 5.5 on both master and slave


texts:
I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived performance benefit.

However, is it appropriate to use it in a relational database? Should we create dummy rows in our base tables so that our relation table can attach to it? Besides being unnatural for RDBMS, it causes unnecessary database bloat. Just wondering if I am NOT seeing something.

All in the spirit of avoiding OUTER JOINs.


texts:
I am trying to speed up the following query, which currently takes 14 seconds:

SELECT
  COUNT(*) AS employee_count, e.jurisdiction_id, e.year
FROM
  employee_employee e
GROUP BY
  e.jurisdiction_id, e.year
ORDER BY
  employee_count ASC;

I add an index by running:

CREATE INDEX ON employee_employee (jurisdiction_id, year);

But that did very little to improve the timing because the GROUP BY is taking the longest to execute. Is there a way to index a GROUP BY function?


texts: GridFS: why can't I shard chunks with a hashed key?

texts:
There is a statement in the MongoDb documentation:

  You cannot use Hashed Sharding when sharding the chunks collection.

It does not say that, but I guess it means that I can only use ranged sharding. But it seems VERY inefficient. In fact, they suggest that the shard key should be the files_id or the files_id + the chunk number. They both increase monolitically. But the documentation also says, that you should not use ranged sharding when the keys increase monolitically. So there is a contradiction here.

Why on earth they do not allow me to use hashed sharding on the chunks collection? At least it should be explained in the documentation.


texts: Handling differences between MS-SQL and PostgreSQL money datatypes

texts:
This is a follow-up/ to my previous question Is it possible to insert un-formatted data for money data type in PostgreSQL

I could read money data by typecasting it into numeric
 SELECT '52093.89'::money::numeric

Will it give inconsistent data, rounded etc.. ?

As per ISO standard, the money datatype size is (19,4) but why PostgreSQL returns size as 2147483647? Is this size locale dependent?

Is it a good idea to use the money datatype to store/retrieve locale specific values? If not, when is the money datatype best suitable?

I have to use the money datatype for certain cases. My worry is will I get any problems if I treat money datatype values as decimal values?
I don't bother about locale specific values such as symbol, digit, and decimal grouping etc.. Can I store money datatype values as per lc_monetary settings and reading by typecasting it to numeric?


texts: mysql: deleted performance_schema, is it a problem?

texts:
Been following this tutorial
https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261

in the process I deleted performance_schema along with the other dbs.

I dont see it reappear after reimporting dbs. Is it ok if I dont have performance schema?


texts: How can I connect SSIS to SQL Server 2000?

texts:
I have to Virtual Machines, one with Windows XP and the other with Windows Server 2008. In my XP VM I have SQL Server 2000 with a database that is my datasource. In WS 2008 I have SQL Server 2008 R2 with an SSIS package to pull data from SQL Server 2000.

My problem: I can not connect to SQL Server 2000, I can not create a connection in my SSIS package. The error says "The connection faild because of an error in initializing provider. Login file for user sa" I am using sa user for the connection and of course this user has access to the database.

Things I have done:

-Both VM do ping between each other.

-I am using Native OLE DB\ Microsoft OLE DB Provider for SQL Server

-Did this configuration from this tutorial: https://msdn.microsoft.com/en-us/library/dd327979.aspx

I continue with the problem, I need to use Sql Server Authentication.
Please if any one has another suggestion, please help.

Regards, Anabel


texts: Create a clustered primary key on partitioned table

texts:
I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is dropped. Again after the partitioning when I tried creating the primary key (clustered) again, It created without any errors. But to my surprise when  I checked the table the partition was removed. Thus on doing more research on this, I found that when we create a new clustered index on a partitioned table the partition goes off.

Can anyone please help me out in creating a clustered primary key on the partitioned table, since non clustered primary key is degrading the performance.


texts: Can the FORCESEEK query hint be used on tables being queried against on a linked server? (I.e. as part of a distributed query)

texts:
I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the original server), I get the following error:

  Msg 7436, Level 16, State 1, Line 4
  The query processor could not produce a query plan because FORCESEEK or FORCESCAN hints on table or view 'TableBeingQueried' cannot be used with remote data sources. Remove the hints and resubmit the query.

Example T-SQL:

SELECT DISTINCT Table3.Field5
FROM Server1.Database1.Table1 AS T1
INNER JOIN Server1.Database1.Table2 AS T2 WITH (FORCESEEK) -- Index exists for T2.Field2
    ON T1.Field1 = T2.Field2
INNER JOIN Server1.Database1.Table3 AS T3 WITH (FORCESEEK) -- Index exists for T3.Field3
    ON T2.Field4 = T3.Field3


texts: Availability mode - manual failover mode, best practice for availability mode?

texts:
We don't want automatic failover for our availability group, but I have set availability mode to Synchronous, was that a mistake?

The availability group runs on vmware with windows 2012R2, and I got the error messages below after the cluster "crashed". (There was some manual migration off the vmware at the same time)

Can I related the crash to availability mode settings?

Thank You in advance.
Regards Odd

Kl 13:58:56 – Critical : Cluster node ‘' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 13:58:56 – Critical : Cluster node '' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 13:59:58: -Critical:  File share witness resource 'File Share Witness' failed to arbitrate for the file share '\\Quorum'. Please ensure that file share '\\Quorum' exists and is accessible by the cluster.
Kl 13:59:58: - Error: Cluster resource 'File Share Witness' of type 'File Share Witness' in clustered role 'Cluster Group' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 13:59:58: -Critical:  The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk.
Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Kl 14:01:22: - Error: Cluster resource '_Grp' of type 'SQL Server Availability Group' in clustered role '_Grp' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 14:01:34: - Error: Cluster resource '_Grp' of type 'SQL Server Availability Group' in clustered role '_Grp' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it.  Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Kl 14:01:34: - Error: The Cluster service failed to bring clustered role '_Grp' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role.


texts:
I need to update millions of rows in postgres and I am trying to write a routine to process these updates in batches. Below is a sample data setup script:
-- drop function if exists dbo.dostuff();
-- drop table if exists wrongtable;
-- drop table if exists righttable;

create temporary table wrongtable (
    thingid int generated always as identity
    , ownerid int
);
create temporary table righttable (
    thingid int generated always as identity
    , ownerid int
);
do
$$
declare i record;
begin
    for i in 1..9 loop
        insert into wrongtable (ownerid) values (12);
        insert into righttable (ownerid) values (14);
    end loop;
end;
$$;

I am trying to write the routine, I have this much so far, ultimatley I want to update in batches of three. I know this doesn't run but its a start
create or replace function dbo.dostuff()
returns void as
$body$
declare numbatches integer := 3;
begin
    update wrongtable a
    set ownerid = p.ownerid
    from righttable p
    where p.thingid = a.thingid
        and p.ownerid != a.ownerid;
end
$body$
language plpgsql;

Thanks for any help provided


texts: SQL 2008 Cluster Install, "Cluster shared disk Availability Check"

texts:
I am trying to setup a new sql server 2008 cluster, on windows 2012R2, and the installer is failing on the cluster shaed disk availability check.  I have verified that there are 5 disks assigned to "Available Storage" when viewed in the fail over cluster manager.

Some background, this is my second attempt to install sql server on this cluster.  The first time, the cluster object was unable to create the new computer object during the cluster installation.  This caused the installer to fail do to lack of permissions.  I have since resolved this, and have run "Remove node from cluster" to uninstall sql server from the node.  I am now trying again to run the installer.

About the environment,
OS: windows 2012R2
SQL Version/Edition: 2008/Enterprise
I am running the installer from the current cluster host(node1), and all storage is owned by node1.  This includes the quorum, as well as the 5 disks assigned to the available storage group.  Both cluster nodes are up and available, and accessible either through the node names, or through the windows cluster name.

There are no cluster validation warnings that I know of, but I have asked the windows admin to rerun the validation tool to confirm that, that is still the case.  SQL 2008 is required by the front end application (I pushed for at least 2012, but was told it was a no go)


texts: Issue with multiple execution of sys.sp_addextendedproperty

texts:
I'm using SQL Server 2008 R2. I need to store a text as a description of each table of my DB. But I don't want to do this with wizard. I handled to build some code for this purpose. Here's a part of the tsql:

begin try EXEC sys.sp_dropextendedproperty @name=N'TableDescription', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='AccountingDynamicPartnerGroupSupplier' end try begin catch end catch
begin try EXEC sys.sp_dropextendedproperty @name=N'TableDescription', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='AccountingZoneShapesPoints' end try begin catch end catch
begin try EXEC sys.sp_dropextendedproperty @name=N'TableDescription', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='CommonAdminPassword' end try begin catch end catch
...
...
begin try EXEC sys.sp_addextendedproperty @name=N'TableDescription', @value=N'شماره تامین کننده برای فروشگاه های زنجیره ای', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='AccountingDynamicPartnerGroupSupplier' end try begin catch end catch
begin try EXEC sys.sp_addextendedproperty @name=N'TableDescription', @value=N'جایگذاری شهر/منطقه/مسیرها در نقشه', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='AccountingZoneShapesPoints' end try begin catch end catch
begin try EXEC sys.sp_addextendedproperty @name=N'TableDescription', @value=N'پسوردهای ادمین', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name='CommonAdminPassword' end try begin catch end catch
...
...

It will be executed fine without any errors. But I have a strange issue after that. I can't select from sys.extended_properties. I noticed that if I execute each line separately it works fine without any problem.

What's the problem? I appreciate your help.


texts: MySQL LEFT OUTER JOIN performance on a local column condition

texts:
Assume the following tables, indexed on the obvious keys:

Table users
------------------------
id | name | has_cars
------------------------
1    Dan    0
2    Bob    0
3    Jake   1
4    Jen    1

Table cars
------------------------
id | user_id | description
------------------------
1    3         Corvette
2    3         Viper
2    3         Lamborghini
2    4         Camry

Is there any performance benefit to the latter of the following two queries?

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON cars.user_id = users.id

vs

SELECT users.*, cars.*
FROM users
LEFT OUTER JOIN cars
ON users.has_cars > 0
AND cars.user_id = users.id

These tables, are of course for sake of example. I'm curious about the implications on much larger tables with very sparse has_cars > 0 condition-meeting-rows. Is the optimizer able to take advantage of using a row-local condition to determine whether or not to attempt the LEFT OUTER JOIN per row?

EDIT: Adding another potential use case to add legitimacy to the question.

Here's a potential use case: let's say you have a table with a type column. For type a rows you want to join the type_a table data, for type b rows you want to join the table_b table data, etc, and here a local condition would determine whether or not to perform a LEFT OUTER JOIN:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON widgets.type = 'a' AND a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON widgets.type = 'b' AND b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON widgets.type = 'c' AND c_widgets.widget_id = widgets.id

For each table join, are you getting any performance benefit from the added local condition (widgets.type = _)? As compared to:

SELECT widgets.*, a_widgets.*, b_widgets.*, c_widgets.*
FROM widgets
LEFT OUTER JOIN a_widgets ON a_widgets.widget_id = widgets.id
LEFT OUTER JOIN b_widgets ON b_widgets.widget_id = widgets.id
LEFT OUTER JOIN c_widgets ON c_widgets.widget_id = widgets.id

(Assuming there is a widgets.type column, and if a row's type is a,b, or c there is a corresponding row in the a_widgets, b_widgets or c_widgets table.)


texts: Email open rate calculations in subqueries with postgres

texts:
So I'm relatively new to PostgreSQL, but here we go:

I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays all pertinent information. The query returns everything how I wanted, except for a crazy impossible open rate. I'm not sure how I can best find the unique open rate by email send.

Here is the query I have so far, which returns valid results for everything but open rate:

 SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  opencount.openRate AS "Open Rate"
 FROM
  "EmailEvents",
  (SELECT
   send_name,
   (100*(SUM(CASE
    WHEN email IS NOT NULL
    AND event = 'open' THEN 1
    ELSE 0
    END) / SUM(DISTINCT CASE
    WHEN email IS NOT NULL THEN 1
    ELSE 0
   END))) AS openRate
  FROM
   "EmailEvents"
  GROUP BY
   send_name) opencount
 WHERE
  "EmailEvents".send_name = opencount.send_name
  GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  opencount.openRate
 ORDER BY
  MIN(timestamp) DESC

Any help would be appreciated. Thanks!!


texts: Does Oracle 11gR2 is supported on RHEL 8 or Oracle Linux 8?

texts: Oracle (afaik) did not updated their compatibility matrix yet. Can I run Oracle 11gR2 (which still is in extended suporte) in the new OS?


texts: How to auto assign ordinal ranking in mysql with as minimal queries for new rows?

texts:
+----+---------+---------+-----------------+
| id | view_id | user_id | ordinal_ranking |
+----+---------+---------+-----------------+
|  1 |      10 |       1 |               1 |
|  2 |      10 |       2 |               1 |
|  3 |      11 |       1 |               2 |
|  4 |      11 |       2 |               2 |
+----+---------+---------+-----------------+

So suppose I want to insert new rows for view_id 12 and for both user_id 1 and 2, these 2 new rows should have ordinal ranking of 3.

How do I do that with minimal number of queries?

Bear in mind, I have many users who may all have different number of views assigned.

Another corollary to the problem is when I need to delete a particular view, say view 10, all the users affected should have the ordinal ranking re-ordered.

How do I do that also with minimal number of queries?

EDIT

A comment was made about the number of views per user. I would like to add that you may safely assume that there is a users table and one column stores the number of views for each user record.

I am also okay if the ordinal ranking is zero-based, ie the ranking is 0, 1, 2, 3, etc

I also prefer not to use stored procedures.

EDIT2:

+----+---------+---------+
| id | name | view_count |
+----+---------+---------+
|  1 |     Sal |       2 |
|  2 |     Sam |       2 |
+----+---------+---------+

Is the users table

the ordinal_ranking merely reflects the personal choice of a user with regards to the views she can view.

E.g. user 1 Sal likes to have view 10 to be ranked higher than view 11.