texts: How to display User Mapping for offline databases?

texts:
When I use the GUI (SSMS) to review the User Mapping, where the Login has mapping to a database that is offline (or otherwise not available) I get the message below and it only displays Mapping for databases that are online.

  One or more databases are inaccessible and will not be displayed in list

All the details should actually be in the Master DB, there should be something I can run that will show me what the full User Mapping is.

How can I display User Mapping for offline databases?


texts: Newbie IT student. Question about a homework assignment

texts:
I'm a new student in an IT program, and I'm trying to make sense of what this means:

  Design for the following non-functional business problem:

  Your business delivers Internet services, by deploying compute
  infrastructure into rented data centers, which have transit
  partnerships.  In order to sustain these services, it is important to
  track all of the infrastructure, so that the status of it may be
  communicated to the applications which rely on it, and in order to
  manage it.  It is moving beyond the scope of a single spreadsheet,
  which one person can administer, and needs to go into a database for
  concurrent access before a infrastructure management platform can be
  selected.

  There are going to be up to 10 data centers, each with 2 to 4 transit
  providers.

  Each data center has between 2 and 50 racks, and two core routers.

  Each rack has 2 power supplies, 2 switches, 1 storage shelf (4U) and
  20 spaces for 1U servers.

  Each storage shelf has 48 SAS SSDs, and each 1U server has 12 SSDs.

  Use modeling to conclude a relational database design which can
  migrate this need out of a single spreadsheet.  Provide evidence of
  the design, by submitting the PostgreSQL DDL.  An export of a
  spreadsheet will be provided, and sample python for populating a given
  model.

  A data set for populating your database, can be found at:
  infrastructure.tgzView in a new window

  Of course, it is a compressed tar file which contains the following
  files: uplinks.csv, infrastructure.csv, and media.csv.

  Data model designs with less abstraction will be the easiest to
  populate with these data.  Focus on pulling out a simple sample of the
  data, in order to prove the population of the set of tables by mapping
  the natural keys, to generated primary and foreign keys.

Now, the class is fine and good and the instructor is funny and intelligent, but I'm not sure what to do as the pace moves a bit quicker than a baby programmer is used to. Would the following steps be right?

Go into Violet UML Editor and craft the database
Export this model into something
Go into the Postgres I set up in linux
use the database our instructor set up, and link that to the assignment

I'm not looking for someone to do my homework, but an explanation of the underlying concepts and some links would be amazing.


texts: Is there any way to get the position of a value in a table returned by a SELECT statement?

texts:
I have the following statement:

SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id

which returns a table like this one for example:

id
1
3
4
5

But what I need is to know the position of a given number. For example 4 has position 2 in the database.

I can do that with normal C code of course but I wonder if I can do everything in SQL, something like this for example:

SELECT ROWNUMBER(id, 4) FROM myTable WHERE aCondition == 1 ORDER BY id

which should return:

ROWNUMBER
2


texts: How do you cope with Database Server Inventory Management

texts:
Is it common for SQL Server DBAs to have to struggle with Server Inventory Management due to multiple parties / departments bringing online their own instances of SQL Server?

If so have you been able to successfully add controls to prevent this problem?
Also how do you monitor your environment on a regular basis to troll for unmanaged instances so that you can proactively prevent embarrassing situations like databases without backups?


texts: filter a table based on a second table matching all criteria

texts:
Having trouble writing a query to return rows from one table matching rows in a second table.  Simple joins I can pull in combinations, but how do I get only those rows that match ALL of the criteria?

For example:

Attribute Table

+---------+------------+------------+
|  itemno | attrname   | attrvalue  |
+---------+------------+------------+
+    1    +  Color     +  Black     +
+    1    +  Connector +  3.5mm     +
+    1    +  Output    +  5W        +
+    2    +  Color     +  Black     +
+    2    +  Connector +  USB       +
+    2    +  Output    +  10W       +
+    3    +  Color     +  Silver    +
+    3    +  Connector +  3.5mm     +
+    3    +  Output    +  5W        +
+    4    +  Color     +  White     +
+    4    +  Connector +  USB       +
+    4    +  Output    +  2W        +
+---------+------------+------------+

Filter Table 1

+------------+------------+
| attrname   | attrvalue  |
+------------+------------+
+  Color     +  Black     +
+  Color     +  Silver    +
+  Output    +  5W        +
+------------+------------+

would return only items 1 and 3, but NOT item 2 because it didn't match both criteria in the filter.

thanks in advance,


texts: Oracle - Should processes limit be lower than sessions parameter

texts:
SQL> select * from v$resource_limit;

RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------   ------------------- --------------- ------------------ -----------
processes       88                  150             150                150
sessions        100                 174             248                248

Assuming that all the sessions are active and doesn't share processes with each other, is it okay to keep the processes limit significantly lower than sessions limit? In all the resources I've read, it's suggested / calculated to keep sessions as processes * 1.5. But what if the active processes reaches its limit and sessions could not able to assign a process to it?
We faced an exception intermittently while establishing a database connection on a successfully running application.
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |  java.sql.SQLException: Listener refused the connection with the following error:
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.bc.driver.PhysicalConnection.<init>(PhysicalCoConnection.java:546)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     ...
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
INFO   | jvm 1    | main    | 2020/07/30 13:35:13 |     ... 54 more

Considering that the MAX_UTILIZATION of processes reached its limit of 150 at some point (and sessions reached upto 174), I am suspecting that there isn't enough processes to assign it to a session which led me to think that processes should always be higher than or equal to sessions parameter.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


texts: Oracle: Scopes of the CURSOR_SHARING parameter

texts: In Oracle databases (12.2 and above), is it possible to configure the CURSOR_SHARING parameter differently for different schemas, tables or views, or is that parameter always global?


texts: Mapping similar products in single product in price comparison website

texts:
I am making a price comparing website. I have data from different stores, but I could not find any way to map similar products to single product.

I have a table 'product1' like this .

  id       title                                               brandName
|  8986 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  8983 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6048 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6109 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  9840 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  8905 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  9097 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|   765 | Samsung galaxy Ace Duos S6802 ( Black)             | Samsung   |       2 |
|   766 | Samsung Galaxy Ace Duos S6802 (Chic White)         | Samsung   |       2 |
| 11880 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
| 11218 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
|   323 | Samsung Galaxy Ace Nxt G313H Black                 | Samsung   |       2 |
|   311 | Samsung Galaxy Ace Nxt G313H White                 | Samsung   |       2 |
|  9099 | Samsung Galaxy Ace Plus                            | Samsung   |       1 |
|  1153 | Samsung Galaxy Ace Plus S7500 Dark Blue            | Samsung   |       2 |

Now I want to map similar products; for example I need results like

title                                  brandIDs
Samsung Galaxy Ace             8986 , 8983 , 6048 , 6109
Samsung Galaxy Ace Duos        9840 , 8905 ,9097 ,  765 , 766
Samsung Galaxy Ace NXT         11880 , 11218 , 323 , 311
Samsung Galaxy Ace Plus        9099 , 1153

Is there any programming logic or mysql which can give me desired result?
I tried different mysql clause i.e LIKE'%%' but it returns me whole data.

Table schema :

CREATE TABLEproduct1(
idint(11) NOT NULL,
titlevarchar(50) NOT NULL,
brandNamevarchar(50) NOT NULL,
  PRIMARY KEY  (id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

INSERT INTO `product1` VALUES (8986, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (8983, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6048, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6109, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (9840, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (8905, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (9097, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (765, 'Samsung galaxy Ace Duos S6802 ( Black)', 'Samsung');
INSERT INTO `product1` VALUES (766, 'Samsung Galaxy Ace Duos S6802 (Chic White)', 'Samsung');
INSERT INTO `product1` VALUES (11880, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (11218, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (323, 'Samsung Galaxy Ace Nxt G313H Black', 'Samsung');
INSERT INTO `product1` VALUES (311, 'Samsung Galaxy Ace Nxt G313H White', 'Samsung');
INSERT INTO `product1` VALUES (9099, 'Samsung Galaxy Ace Plus', 'Samsung');
INSERT INTO `product1` VALUES (1153, 'Samsung Galaxy Ace Plus S7500 Dark Blue', 'Samsung');

Can any body please tell me how can i achieve desired result in mysql.


texts: Strange Issue with Linked server and using IF exists

texts:
We are trying to insert data from remote server using below query

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
begin
Insert into [ServerB].[DB_B].dbo.Monthlydata
select  *,getdate() from
[ServerA].[DB_A].dbo.monthlydata
end

Above query fails with below error

  The OLE DB provider "SQLNCLI11" for linked server "ServerA" does not contain the table ""DB_A"."dbo"."MonthlyReportData"". The table either does not exist or the current user does not have permissions on that table.

Table Doesn't exist,but whats strange is  query checking   `begin' block ,even after if exists fails

If we modify the query to below,it works with out any error and print 1 doesn't gets printed

  if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    print 1
    end

if we modify further the query to, query a table which exists it work,i mean it doesn't go further after begin block

 if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from
    [ServerA].[DB_A].dbo.validtable
    end

In the above example,in begin block,observe exists works correctly and doesn't throw any error only when you have a valid table in begin and end block''

Hope i explained this clearly.

Below are few things checked

1.Issue Exists even with information_schema.tables
2.Tried this as well,but no help  :https://stackoverflow.com/questions/22182437/check-if-table-exists-in-external-linked-database
3.Account used to connect is Sysadmin

Below answer works when i use a single statement and use exec.I should have made myself clear,above query is generated dynamically and passed to exec..see below example

set @Sql='

Insert into [server_A].[db_A].dbo.Monthlydata
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(cast(@dbname as varchar(20)))+'.dbo.MonthlyReportData
end'

exec(@sql)

Please let me know if i am unclear or you need any further info

Below is my complete code

DEclare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open GetBMSdata
fetch next from Getdata into @dblocation,@dbname

while @@FETCH_STATUS=0
begin

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData)
begin
Insert into [servera].[dba].dbo.Monthlydata
select  * from ' +quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData
end'

exec(@sql)

fetch next from Getdata into @dblocation,@dbname
end

close Getdata
deallocate Getdata

I ended up modifying my code like below

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.sys.tables where name='MonthlyReportData'')
begin
exec(
''
Insert into [serverb].dba_b.dbo.MonthlyReportData
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.dbo.MonthlyReportData
''
)
end
'
print @sql
exec(@sql)


texts:
How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?

For instance:

The restore process will create:

bob.mdf
bob.ldf

etc...


texts: How to configure an instance to listen on port 1433 to allow remote connection

texts:
Trying to connect using SQL Server Management Studio 2012 on a Windows 8.1 Client computer from outside the cluster's domain.

Cluster is a two node Virtual Cluster made from Windows Server 2012 R2.

We can connect to the cluster from within the cluster's domain by using the Failover Cluster Virtual Network Name Account with SSMS running on another Virtual Computer from within the cluster's domain.

We have created a new Inbound Rule with port 1433 on each of the two nodes - but still don't understand how to connect to the SQL Instance from outside the domain.


texts: Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?

texts:
I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause.

set up:

create table test_t ( x varchar2(100));
insert into test_t values ('it''s a string');
commit;

Dummy Query:

select * from test_t
 where x like '%''%' escape '''';


texts: How do I disable constraint checking during schema migration?

texts:
How do I disable FOREIGN KEY checking for the time of database schema migration?

Currently I found engine-based commands like:

PostgreSQL: SET CONSTRAINTS ALL DEFERRED;
MSSQL: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
MySQL: SET FOREIGN_KEY_CHECKS = 0;
HSQLDB: SET REFERENTIAL_INTEGRITY FALSE;
DB2: SET INTEGRITY FOR table OFF;
SQLite 3.7.16: PRAGMA foreign_keys = false;
Oracle 11g seems to have no such command at all

Syntax significantly differ, in case of MSSQL I'm unable to recover previously disabled constraints (if any).

I look for possibly database-independent solution, or at least commands working for whole database of single engine. We can assume there is no constraint violation at the end of migration. Migration will be conducted in a transation.


texts: Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input

texts:
I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of these types via the procedure.
Just to put this out there: this code works without the array of numbers (i.e. if I were to just remove the variable as_ids from the table, composite type, and procedure, the code runs the way its supposed to. for some reason it just doesn't work with the array).
The table
CREATE TABLE user_ans(
ans_id INT GENERATED ALWAYS AS IDENTITY,
fq_id INT NOT NULL,
p_id INT NOT NULL,
j_id INT NOT NULL,
u_id INT NOT NULL,
d_id INT,
ans TEXT NOT NULL,
as_ids INT[] NOT NULL,
created TIMESTAMP,
updated TIMESTAMP
);

The composite type
CREATE TYPE new_ans_obj AS (
    ans TEXT,
    as_ids INT[],
    created TIMESTAMP,
    d_id INT,
    fq_id INT,
    j_id INT,
    p_id INT,
    u_id INT
);

The procedure
CREATE OR REPLACE PROCEDURE add_ans_arr (
    new_ans new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
    SELECT * from unnest(new_ans);
    COMMIT;
END;$$;

Attempts
Command I use:
call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,{2002,2005},2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:
ERROR:  malformed array literal: "{2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:4...
                                   ^
DETAIL:  Unexpected end of input.

Command I use:
call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:
ERROR:  malformed array literal: "(ARRAY[2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021...
                                ^
DETAIL:  Array value must start with "{" or dimension information.

Post obvious attempts
I've also tried different variations of the two above commands (using parenthesis, putting the size of the array in, etc), but with similar outputs. I've read other posts about inserting arrays into Postgres, as well as the documentation and it seems like I'm using the right syntax (although probably not)?
I was thinking that the array of integers might be conflicting with the 'unnest' function, so I rewrote the procedure to following, but still the same problem pops up:
CREATE OR REPLACE PROCEDURE add_ans_arr (
    arr new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
DECLARE
    ans_obj new_ans_obj;
BEGIN
    FOREACH ans_obj IN ARRAY arr
    LOOP
        INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
        VALUES (ans_obj.ans, ans_obj.as_ids, ans_obj.created, ans_obj.d_id, ans_obj.fq_id, ans_obj.j_id, ans_obj.p_id, ans_obj.u_id);
    END LOOP;
    COMMIT;
END;$$;

I'm able to individually insert these objects into the table:
insert into user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id) VALUES ('lala', '{2002}', '2021-01-19T21:48:29.626Z', 1001, 7002, 3, 6001, 1);

But I don't really understand why the above method isn't working? I've tried putting the single quotes into the my first command, but I just get a syntax error. Also I don't want to mess too much with the
ARRAY['','']::ans_obj[]

structure because it works for the rest of my other procedures, and this structure is generated by my server. Although I'm open to change it the server-side code if there's something horribly wrong with this structure, but again - it works for the rest of my stored procedures.
Any direction/help would be greatly appreciated.


texts: Postgres 9.6 export the database structure as pdf

texts: Is there any tool/software the can export all the tables structure (column names , its datatype ) in the database as a pdf/excel/word. additional diagram export will be added advantage.


texts: What's the best way to programatically have a MySQL DB update another MySQL DB

texts:
I am currently working for a service provider that offers Windows software for inventory control. This software connects to a MySQL DB on a dedicated server.

So when this software is updated it sends the update to the DB. What I'd like to have happen, is when this update is made in the main DB it somehow updates another MySQL DB, which happens to be WordPress.

I just don't know what the "somehow" is right now. Not sure if that's a cron job or something else where the main DB is constantly monitored.

Any and all help is appreciated


texts: Big table size in PostgreSQL while number of rows is small

texts:
Tables' size in the PostgreSQL DB are big while the number of rows is relatively small (less then 500K rows).
Vacuum of the specific table did not solve the problem.
Only by deleting and importing the table from a previous dump file did solve the problem: the tables' size are back to normal. Consequently, all the queries on these tables became very fast while they were lasting for long before the import operation.
Note: auto vacuum is on by default


texts: Automatic database restore fails as cannot find file

texts:
A while back I asked for help automating a database restore using a backup file provided from another server: Automatically restore SQL Server database from file from another server. I'm pleased to say that I now have this working as a SQL Server Agent job.

We are now trying to trigger this job as part of a maintenance plan that runs on the other (dev) server. At the moment we have a maintenance plan that runs on the live server and performs the following steps, in sequence:

Back up the target databases
Call an external process to copy the created backup files
Clean up any backup older than 2 days (runs at the same time as 2)
Start the restore job on the other server

When I run the maintenance plan on live it reports success, however when we check the job history on the dev server it shows that it has failed saying that it could not find the file. Running the job again returns success, and a manual check indicates that the files have been copied correctly.

It would seem that the restore job is being started too soon but I can't figure out why. Is there any reason why the restore job is being triggered before the copy process has completed?

EDIT: I've checked the maintenance plan logs and the restore job step starts 1 second after the transfer step. Obviously the transfer takes longer than this, so I can at least see evidence of the problem.


texts: How to handle "reference tables" in CouchDB?

texts:
What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food?

Multiple documents, one for each ingredient
One document per reference

{
  "_id" = "ingredients",
  "ingredient": [{ "name":"Apple", ... }]
}
One document with all references

{ "_id" = "references", "ingredients": [{"id": "apple", "name": "Apple", ...}], "spices": [{"id": "cinnamon", "name": "Cinnamon", ...}]
}


texts: How does SELECT FOR UPDATE behave with views in PostgreSQL?

texts:
To retrieve domain model data from PostgreSQL, I sometimes select against a view rather than a table. I do this so that I can return as much information as possible in one query, rather than fire off multiple queries to resolve all relational dependencies. Mostly this is because there is a 1-to-1 relationship between tables, or they are lookup tables and I just need certain columns, etc. To prevent certain race conditions, one strategy I'm using is SELECT ... FOR SHARE/UPDATE queries. I'm wondering how these behave when we start using them against views.

Suppose we have two tables A and B, each with a column ID. Now, suppose we have a view AB that is a join operation between those two tables. Let's assume this is a Cartesian join:

SELECT
    "A"."ID" AS "AID",
    "B"."ID" AS "BID"
FROM "A"
CROSS JOIN "B"

If we then SELECT FOR UPDATE against AB what rows of what tables will get locked in the following instances?

SELECT * FROM "AB" WHERE "AB"."AID" = :ID
SELECT * FROM "AB" WHERE "AB"."BID" = :ID
SELECT * FROM "AB" WHERE "AB"."AID" = :ID1 AND "AB"."BID" = :ID2


texts:
When i try to connect a linked server, i´ll get this error message :

TITLE: Microsoft SQL Server Management Studio

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

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476

I have tried resolve by steps described in here :

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1121ae61-1224-406a-8b8a-49f236df3edf/cannot-setup-a-linked-server-in-sql-server-get-this-connection-error-message?forum=sqlexpress

But still no change,
I have set TCP/IP protocol enabled in SQL conf. manager for both servers.
I have tried to ping first PC from the second and it worked.
I have set the Incoming TCP rules for 1433 port in firewall for both servers.
I have  set the incoming UDP rules for 1434 port in firewall for both  servers.
( I did not set the same rules for Outcome )   -- could it be the problem ?

Remote connections on both servers are enabled,

I want to connect MS SQL 2008 with MS SQL 2014 could it be problem with different versions ?


texts: Get the latest change in value from a streak of values


texts:
I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code I want to implement as a process as follows:

The application server has a Java program that acts like a database front end for clients. Basically, a client wishes to do some number crunching, submits some data to the Java tier, and the Java tier saves this data to the database, along with a flag used to instruct a C program that data is waiting.

The application server also has this C program on it. The C program polls the database for this flag every 1 second or so, and when the C program detects this flag to be set, the C program downloads the data using OCILIB (e.g. OCI) from the database, crunches the math, and saves a result back into the database. The C program then sets a different flag to instruct the Java tier this processing is done.

Another program in the Java tier is watching this flag that the C program will set. When it detects the flag is set, the Java tier sends a message to the client telling the client that the data is available in the database.

The client then downloads the data from the database by sending an appropriate request to the first Java program discussed above.

[A simpler approach may be to have the java tier call the C program directly (to avoid all of this polling the database, etc.), but the required JNI programming along with java queuing and multi-threading is beyond my capability as a programmer.]

QUESTIONS:

Is having 2 processes poll the database fairly common in the industry (and nothing to worry about), or is it a bad practice?

Is there a way, for example (in SQL or PL/SQL), to avoid polling by having the database call a function (e.g. C or Java) when the contents of a cell change to become a certain value (e.g. 0 = wait, 1 = call the function)?

Is the simplistic approach above reasonable or flawed somehow? Is there a better, although still straightforward, way of achieving the same result? Anything to watch out for?


texts: Replication with Always On availability groups

texts:
I have a three server set up:

SQL01 – Primary server
SQL02 – Secondary server
SQL04 – Remote distribution server
SQL04 – Subscribe

SQL01 / SQL02 DB's are part of basic availability groups and i am trying to configure transaction replication of some of these databases to SQL04.

SQL04 is the remote distributor.

I have been following this Microsoft guide.

My issues comes at the validation of the replica hosts as publishers sproc ( the final step!)

USE Distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
    @original_publisher = 'SQL01',
    @publisher_db = 'DB',
    @redirected_publisher = 'AG-listener';

I receive the error stating read access is not enabled, the same error which is stated in the MS guide, but i cannot work out how to step over it!

error:

  The query at the redirected publisher 'SQL02' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQL01' failed with
          error '976', error message 'Error 976, Level 14, State 1, Message:
  The target database, 'db',
          is participating in an availability group and is currently not accessible for queries.

  Either data movement is suspended or the availability replica is not enabled for read access.

  To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.


texts: Export Oracle Database User data from client

texts:
I am working on client system. There is a database on server contains many users. I have the access to database with a userid and password. my userid is "ncon" and Database name is 'HYDPPD'.

We are maintaining a Web-application with this database. Now we want to migrate database to another server.

my system is having toad version 9.5.0.31.
and oracle client version installed.

I want to export complete "ncon" user data from database on client system. user data contains 470 tables, 450 procedures, 20 views, 15 triggers and some functions.

I tried:

D:\>exp ncon/nconac_2014 file=ncon.dmp owner=necon

Export: Release 8.1.7.0.0 - Production on Sat Mar 15 19:56:15 201

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

EXP-00056: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
EXP-00000: Export terminated unsuccessfully

D:\>

Please any one help me out.
I have very less time to export data from database.

Thanks in advance.


texts: strange situation with very large partitioning table

texts:
I have a very strange situation here...

On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last partition has a boundary 201205110000.

All Data after 201205120000 is before the last boundary 201205110000, now i thought I can make the new boundary ranges, but the fi case is, If I make the next boundary 201205120000 sql server copy all data from 201205110000 to the boundary 201205120000, this makes for me sense, but I can’t do this because my transaction log becomes full. also the specific file, in this case file_20120512, is growing.

If thought I can create the last boundary 201206040000, then 201206030000, but if I do this SQL Servers  tran log runs full but the specific data file file_20120604 does not growing but the tran log …

What can I do?


texts: Why does SAMPLE BLOCK often return zero?

texts:
In a comment on another question, someone asked:

  I used SAMPLE BLOCK in many tests but found that it would inexplicably return '0' much of the time. Any idea why?

I speculated that this might be to do with the sample hitting empty blocks but even if you tune the table to make sure all the extents allocated are completely full, the behaviour doesn't change, so it can't be that.

test data:

create table foo(bar integer primary key, baz char(1000));

insert into foo(bar,baz) select level, 'A' from dual connect by level<=1000;

begin
  dbms_stats.gather_schema_stats(null);
end;

select sum(blocks) blocks from user_extents where segment_name = 'FOO';

| BLOCKS |
| -----: |
|    256 |

test SAMPLE BLOCK:

with w(estimated_rows,distinct_blocks) as(
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50))
select * from w;

ESTIMATED_ROWS | DISTINCT_BLOCKS
-------------: | --------------:
             0 |               0
             0 |               0
          2000 |             143
          2000 |             143
          2000 |             143

dbfiddle here

I found a Jonathan Lewis blog on a related issue in 10.1, but this didn't explain why it is happening or how to work around the issue.


texts: Is it possible to add a spatial index to a partition with Postgres 10?

texts:
I am creating declarative partitions like:

CREATE TABLE mydata.my_features (
    id bigint,
    my_feature1 character(1),
    my_feature2 integer,
    ...
    my_geom geometry
) PARTITION BY LIST (my_feature1);

And using some ad-hoc functions:

CREATE TABLE partitions.%I PARTITION OF cores.rents_core FOR VALUES IN (%L) PARTITION BY LIST (my_feature2)

CREATE TABLE partitions.%I PARTITION OF partitions.%I FOR VALUES IN (%L)

The partitions get created and populated successfully once the data get inserted.

However, when I create the table at the beginning, if I try to add an index like suggested in this Postgres official documentation resource:

  Create an index on the key column(s), as well as any other indexes you might want for every partition. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)

like a spatial index:

CREATE INDEX "mydata_my_features_geom_idx" ON mydata.my_features using gist(geom);

I get an error like:

  ERROR:  cannot create index on partitioned table "my_features"

Is that actually possible add this kind of index or I am doing something wrong?


texts: Postgres JOIN conditions vs WHERE conditions

texts:
Postgres newbie here.

I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See below.

SELECT *
    FROM
      myapp_employees
    JOIN myapp_users ON
      myapp_users.user_id=myapp_employees.user_id
    JOIN myapp_contacts_assoc ON
      myapp_contacts_assoc.user_id=myapp_users.user_id
    JOIN myapp_contacts ON
      myapp_contacts.contact_id=myapp_contacts_assoc.contact_id
    WHERE
      myapp_contacts.value='test@gmail.com' AND
      myapp_contacts.type=(1)::INT2 AND
      myapp_contacts.is_primary=(1)::INT2 AND
      myapp_contacts.expired_at IS NULL AND
      myapp_employees.status=(1)::INT2 AND
      myapp_users.status=(1)::INT2
    LIMIT 1;

Note: For context, this proc is checking to see if a user is also an employee (elevated privs/different user type).

Anyways, is this the right way to go? Should the JOIN ON contain more statements like checking for expired_at IS NULL, for example? Why or why doesn't this make sense?


texts:
When doing an insert, an empty string is converted to null:

insert into test (f) values ('');

Now, there is a row with f containing a null.

But, when I query the table, I cannot use '':

select * from test where f='';
no rows selected

I can use null:

select * from test where f is null;
____F_
NULL

So... it appears that Oracle decided that empty strings cannot be used for insert,  but they remain empty strings when doing queries. Where is the documentation on when an empty string becomes a null and when it remains an empty string?


texts: Handling MySQL length=22 and decimals=31

texts:
I'm using MySQL's C api to determine the maximum possible value that can be stored in a specific field.

I have the following table:

CREATE TABLE test (
    f1 double(4,2),
    f2 double
)

When I get the field information for f1 and f2 using the API, here is what I see:

f1: { [...], length = 4, decimals = 2 }

f2: { [...], length = 22, decimals = 31 }

I understand that the maximum value that f1 can store is 99.99 (total length = 4, so 4-2=2 digits before the decimal and 2 digits after the decimal).

Is it safe to assume that if a column type is DOUBLE, its length is 22 and its decimal is 31, then there is no (m, d) specified? If so, then can we also assume that the maximum value this column can store is 1.7976931348623157E+308 (as per the docs)? Can this value ever change between different machines/operating systems?


texts: Enforce uniqueness across optional attributes for an entity

texts:
I have an entity that has multiple attributes, and these attributes are themselves entities.

No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. But at the same time, not all attributes are required.

The following DML illustrates this where attr_a and attr_b are not optional (do not allow NULL) whilst attr_c and attr_d are optional (do allow NULL):

CREATE TABLE attr_a (
  attr_a_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_b (
  attr_b_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_c (
  attr_c_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_d (
  attr_d_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;

CREATE TABLE entity (
  entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  attr_a_id INT(10) UNSIGNED NOT NULL,
  attr_b_id INT(10) UNSIGNED NOT NULL,
  attr_c_id INT(10) UNSIGNED NULL,
  attr_d_id INT(10) UNSIGNED NULL,
  UNIQUE KEY uq_attr (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
  KEY `fk_attr_a_id` (attr_a_id),
  KEY `fk_attr_b_id` (attr_b_id),
  KEY `fk_attr_c_id` (attr_c_id),
  KEY `fk_attr_d_id` (attr_d_id),
  CONSTRAINT `fk_attr_a_id` FOREIGN KEY (attr_a_id) REFERENCES `attr_a` (attr_a_id),
  CONSTRAINT `fk_attr_b_id` FOREIGN KEY (attr_b_id) REFERENCES `attr_b` (attr_b_id),
  CONSTRAINT `fk_attr_c_id` FOREIGN KEY (attr_c_id) REFERENCES `attr_c` (attr_c_id),
  CONSTRAINT `fk_attr_d_id` FOREIGN KEY (attr_d_id) REFERENCES `attr_d` (attr_d_id)
) ENGINE InnoDB;

So the intention is that these two rows from the entity table should not be allowed:

1,10,20,30,NULL
2,10,20,30,NULL

The following pairs of rows should be allowed, as though nulls were actually values (I understand the BDB engine would allow this, but I will be using InnoDB.)

1,10,20,30,NULL
2,10,20,NULL,NULL

1,10,20,30,40
2,10,20,NULL,NULL

1,10,20,30,NULL
2,10,20,NULL,40

The tables described above is obviously flawed, because of how NULLs are handled with a uniqueness constraint -- it would allow those two rows to exist.

In the case of an entity, attr_c and attr_d are optional rather than unknown. So I think allowing NULLs is not technically correct because the value is known to be undefined, rather than unknown.

I think the only practical solution is to reserve a item in each of the attribute tables for "undefined" (possibly with ID 0.)

I know this solution would work, but I want to avoid the mistake of adopting a known anti-pattern.


texts: I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to establish the correct normalized form and relationships that would address one device record having an arbitrary number of cards and those cards having an arbitrary number of ports. Any thoughts?


texts: Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?

texts:
I was working on a query at work, that had a left join like

cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x

The actual execution plan for this query was fairly close, est 269 vs actually 475.

Changing the right +padding to use format(colb, '000') results in a a huge mis-estimate of the number of rows, off by at least 4 million, which causes the query to take 10-15 times as long.

I understand why a mis-estimate would cause a problem, but I don't understand why using Format would cause a less accurate estimate.


texts: Updating a table with a not-joined query

texts:
I'm using Access and have a task that I want automated using an query.

I have a table (IncentiveAssignments) that lists out all of the Gift Card numbers (GiftCode, all are unique) I currently have along with the people I've given them to (PersonID, all are unique).  Not all of the GiftCodes have been assigned a PersonID yet.  There's also a primary key autonumber and some other irrelevant fields.

I also have a query (UnassignedPeople) that lists out all of the people (PersonID) that are currently not listed in IncentiveAssignments but need to be where there is an IncentiveAssignments.GiftCode with a null value in IncentiveAssignments.PersonID.

So this is my IncentiveAssignments table:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       .
4        mnop       .
5        qrst       .

And this is UnassignedPeople:

PersonID
--------
56
78

I want to create a query that will update IncentiveAssignments to be:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       56
4        mnop       78
5        qrst       .

Does anyone have any idea how I can do this?  I'm currently just copy-pasting what's in the query into the table, but I'd much rather it be automated that way I don't worry about human error.  It seems like my primary issue is that there's nothing to join the table and query together.

Much appreciated.


texts:
Does anyone know how to tell how many resources (CPU/MEM/Reads etc) have been used by SQL Server Extended Events?

I've went through the available DMVs here
But I dont see where this is tracked anywhere.

I'm asking b/c I worked on a poorly performing sql server for a few days before I realized there was an EE session running that, when killed, cut CPU utilization in half. (It was Idera Sql DM running the EE Session)


texts:
I'm running a database with a few tables with large row counts (millions) but which are fairly small in data size. All the tables are append-only, so no deletes or updates ever happening, around 250 rows are added per minute. 99% of the time queries take less than 5ms. However, occasionally queries get terminated by Postgres for taking more than 200ms (my configured statement timeout).

I'm using default settings apart from autovacuum_analyze_scale_factor=0.05, autovacuum_vacuum_scale_factor=0.1 and autovacuum_naptime=30.

I'm running PostgreSQL 9.6.3 on AWS RDS on a t2.medium machine, with multi-az and connecting to it from AWS Lambda (Node.js 6.10) from within the same private VPC.

CPU stays under 7% at all times, disk read latency is less than 2 ms, number of concurrent connections rarely goes above 1. Average queries per second is quite consistent at around 20.

We've also tried turning synchronous_commit to off, but the same timeouts continued to happen.

Could this be an issue with the auto-vacuum process slowing queries even though I've got no updates or deletes?

Update: Having removed all writes to the database, we still see the same issue. So, no inserts, no updates, no deletes, just ~30 selects per second causes timeouts.


texts:
We're currently using Clustering to do fail over for our SQL environment.

There are two servers that use the same shared drive.  The SQL database is on a shared drive.  This provides OS and hardware failover, however there is only a single copy of the database.

What we need is two copies of our database.  We need this to allow us to roll changes into production safely.  The deploy process would be to have one the web server point at one SQL Server, while we failover the other server to do the deployment.  Then we would point reverse this configuration to update the first SQL Server.

Does anyone know how to use clustering to do that?


texts: PostgreSQL operator uses index but underlying function does not

texts:
I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a simple table:

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings VALUES
    ('{"name":"First","tags":["foo"]}')
  , ('{"name":"Second","tags":["foo","bar"]}')
  , ('{"name":"Third","tags":["bar","baz"]}')
  , ('{"name":"Fourth","tags":["baz"]}');
CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

Using the command line I can run a simple select and it uses the index as expected:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000 width=61) (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00 rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((d -> 'name'::text) ? 'First'::text)
 Planning time: 0.073 ms
 Execution time: 0.047 ms
(7 rows)

Since I cannot use the ? character I resorted to using a function which underpins the ? operator. However, it is not using the index:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE jsonb_exists(d->'name','First');
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
   Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.051 ms
 Execution time: 3135.138 ms
(5 rows)

Why is this happening, and what can I do to make the function use the index? Note that in reality the table has another 10MM rows in it and I also have enable_seqscan turned off so this isn't a case of the planner deciding not to use the index.

In response to a comment I tried using a custom operator instead:

CREATE OPERATOR ### (
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

But that has the same problem:

EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000 width=61) (actual time=0.012..3381.608 rows=1 loops=1)
   Filter: ((d -> 'name'::text) ### 'First'::text)
   Rows Removed by Filter: 10000003
 Planning time: 0.046 ms
 Execution time: 3381.623 ms
(5 rows)

Update

The latest PostgreSql driver (as of March 2015) has the ability to escape the ? character so this specific case is no longer a problem.


texts: Shrink Transaction Log While Using AlwaysOn Availability Group

texts:
We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database.

I have read here doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable. Anyway, the transaction log backup size is big and can be reduce using shrink file:

I have restore the database locally and perform the shrink operation. The log file size was reduces to 160 MB.

My question is on which database should I perform a shrink operation over the transaction log file (primary, secondary or both)?

I guess in the past for several years no back ups of the log file are made, so it become so huge. Executing DBCC SQLPERF (LOGSPACE) I can see that only 0.06% of the file are used - there is no point for me to keep such huge size of the log file. In [sys].[database_files] I check that its max_size is set to -1 with growth to 65536 so I guess when it need more space it will get. Anyway, I can shrink it to 5% for example in order to prevent future growth. I am trying to find some confirmation that I it is not bad idea to do so.

Actually, back ups (on the database and the log files) are performed only on the secondary databases, so it will be easier to perform the shrink file on them, but will the primary log file size be reduced as well?


texts: Mongo Update keeps adding to the the size of the collection

texts:
I am running a scraper to check for this one thing to change. So I have no idea why my mongo data base keeps growing when all I do is an update on this single data.

I only have one item in the database, with a post_id : 1. In my code all I am doing is updating it. (python)

client = MongoClient(DB_URL)
db = client.chatScrape

collection = db.posts

#insert scraped data
result = collection.update({'post_id' : 1}, scraped, upsert = True)

client.close()

So after the first execution I get

> db.posts.find().count()
> 1

But after an hour or so I get

> db.posts.find().count()
> 2

After about a week or so, the number grows to 10+. So what is going on here. Shouldn't update only modify one item and not make more?


texts: How to get the top row value in insert of temporary table

texts:
insert into @Temp([dateTime],Reading)
values (
@startDate,
(select top(1) @dynamicCOLUMN from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))

I want the value of dynamic column instead it returns columns name in select subquery


texts: postgresql does not seem to respect the pg_hba.conf config - how can I diagnose this?

texts:
I have the following pg_hba.conf:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

With the above I assume host    all             all             127.0.0.1/32            trust would allow TCP connections from localhost. However this does not seem to be the case
[root@XenonKiloCranberry:~]# psql -U postgres
psql (11.7)
Type "help" for help.

postgres=# \q

[root@XenonKiloCranberry:~]# psql -U postgres -h 127.0.0.1
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

Where am I going wrong?
Output of select * from pg_hba_file_rules:
line_number | type  | database | user_name | address |                 netmask                 | auth_method | options | error
-------------+-------+----------+-----------+---------+-----------------------------------------+-------------+---------+-------
           1 | local | {all}    | {all}     |         |                                         | trust       |         |
           2 | host  | {all}    | {all}     | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         |
(2 rows)


texts: Perform TABLESAMPLE with WHERE clause in PostgreSQL

texts:
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.

This runs fine:

select * from customers tablesample bernoulli (1);

But I cannot figure out how to embed the condition in the script. This for example

select * from customers where last_name = 'powell' tablesample bernoulli (1);

throws this error:

  SQL Error [42601]: ERROR: syntax error at or near "tablesample"
  Position: 71


texts: Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1

texts:
When I am gathering schema stats with below options, it is taking too much time:

EXEC dbms_stats.gather_schema_stats (
   ownname => 'SCHEMA',
   granularity => 'AUTO',
   CASCADE=> true,
   estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
   method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   degree => 16
);

Elapsed: 05:36:38.76

SCHEMA_SIZE_IN_GB
-----------------
       160.828857

As per oracle doc,

If incremental need to be used then there are several conditions which need to be true in order to take advantage of the incremental statistics collection:

When the following criteria are met, the database updates global statistics incrementally by scanning only the partitions that have changed:

1) The INCREMENTAL value for the partitioned table is true.

2) The PUBLISH value for the partitioned table is true.

3) The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

Do I need to satisfy all these three above mentioned conditions then incremental statistics work?

Oracle DB version-12.2.0.1.0

Storage: Oracle FS1-2 storage with 1.2TB /1.6 TB SAS disks.

OWNER       SEGMENT_TYPE         SIZE_IN_GB
================================================
XXXXX       INDEX SUBPARTITION   87.50
XXXXX       TABLE SUBPARTITION   48.71
XXXXX       TABLE PARTITION      14.66
XXXXX       INDEX PARTITION       9.84
XXXXX       INDEX                 0.06
XXXXX       TABLE                 0.03

The output of select dbms_stats.get_prefs('INCREMENTAL','SCHEMANAME','TABLENAME') from DUAL;
is false.
I have total 345 tables in schema, out of which 320 tables is partitioned(Each table have 11 partitions) . Out of 320 partitioned tables, 239 tables have 0 subpartitions and 81 tables have some random number of subpartitions.

So instead of setting incremental preference on each table, can't we set it on whole schema.I did find options are available but not sure about it,how this settings works at partitions and subpartition level.


texts: Referring to a network location without volume label in VBA for Access 2013

texts:
I run a database at work in Access 2013. The VBA corrupted after a day from the amount of people using it, so I attempted to split the database to solve the problem. The split worked after I decompiled the database, but then when other users tried to access it, the front end couldn't find the back end. This is because we all have the network drive it is mounted on assigned a different volume label - ie mine is set to W:/ and others have H:/, X:/, etc.

As the database splitting is a built-in function, as far as I could see there is no way to edit the attributes of the split, or even choose where to save the back-end. I'm pretty sure there's no way around this (but if there is, please let me know) so instead I'm just going to make sure I back up the database every day. I found some code for it in this thread Backup Access Database Daily/Weekly

Which backs the database up every time it's opened, but again it refers to the location to back it up in with the specific volume label. Is there any way to refer to the location by its full location address rather than W:\location? The full name of the network drive looks something like \filesvr2\NetworkFolder\SubFolder\Database

Any help with my first post would be appreciated!


texts: How do I get the count for time periods?

texts:
I have a table with the following columns.

[ComputerGroupid], [Computerid], [Userid], [loginTime], [logoffTime]

And my requirement is to get the table with the below information

[ComputerGroupid], [StartTime], [endTime], [numberOfcomputerBeingUsed]

But I am not sure how to merge the date ranges when they overlap and also when the number of computers being used are equal?

edit:
Some more details.
What I am looking for is to get a table of computer usage for a group of computers. so I am expecting to create a table where when ordered by time the [numberOfcomputerBeingUsed] column goes up as people log into computers and the [StartTime] and [endTime] columns will match up.

eg: if 4 people come into a room and login at about the same time, for an hour, and one person comes in and logs on for 5 mins and then leaves. I would expect:

 [ComputerGroupid] | [Computerid] | [Userid] | [loginTime] | [logoffTime]
 A                 | 1            | 1        | 09:00       | 10:00
 A                 | 2            | 2        | 09:01       | 10:01
 A                 | 3            | 3        | 09:02       | 10:04
 A                 | 4            | 4        | 09:03       | 10:03
 A                 | 5            | 5        | 09:10       | 09:15

  [StartTime]  |  [endTime]  |  [numberOfcomputerBeingUsed]
  09:00        |  09:01      |  1
  09:01        |  09:02      |  2
  09:02        |  09:03      |  3
  09:03        |  09:04      |  4
  09:10        |  09:15      |  5
  09:15        |  10:00      |  4
  10:00        |  10:01      |  3
  10:01        |  10:02      |  2
  10:02        |  10:03      |  1
  10:03        |  10:04      |  0


texts: SSMS How to Click within Query to Open Table

texts:
I come from oracle SQL Developer world where I can ctrl+click on a table name within a query and the table definition automatically opens.  This is great as it alleviates my need to navigate within the Tree and drill down until I find the table I'm looking for.

How do I do this in SSMS?  I have looked online however I have not found any answers that show this is even a possibility.

A quick search for Oracle comes up with this.

But again since I no longer work with Oracle how do I do this same thing in SSMS?


texts: How to prevent SQL Server database server from running out of disk space?

texts:
What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ?

Is there a way to set the maximum database file size (both the PRIMARY and the LOG) ? Once the maximum size reach, it should delete older record to give more free space ?

Notes: I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.


texts: SQL Select taking too much time to execute

texts:
It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table.

In code:

SELECT
    TempTable.Col1,
    TempTable.Col2,
    TempTable.Col3,
    JoinedTable.Col1,
    JoinedTable.Col2,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn1,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is also ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeOtherColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn2,
FROM
    #TempTable as TempTable
LEFT JOIN
    JoinedTable
ON (TempTable.PKColumn1 = JoinedTable.PKColumn1 AND
    TempTable.PKColumn2 = JoinedTable.PKColumn2)
WHERE
    JoinedTable.WhereColumn IN  (1, 3)

This is an exact replica of my query.

If I remove the two sub selects, it runs just fine and quickly. With the two sub selects, I  get about 100 records per second, which is extremely slow for this query because it should return almost a million records.

I've checked to see if every table has a Primary Key, they all do. They all have Indexes AND statistics for their important columns, like the ones in those WHERE clauses, and the ones in the JOIN clause. The only table with no primary key defined nor index is the temporary table, but it's not the problem either because it's not the one related to the slow sub selects, and as I mentioned, with no sub selects it runs just fine.

Without those TOP 1 it returns more than one result, and raises an error.

Help, anyone?

EDIT:

So the execution plan told me I was missing an Index. I've created it, and recreated some of the other indexes. After a while, the execution plan was using them, and the query now runs fast. The only problem is I'm not succeeding in doing this again on another server, for the same query. So my solution will be to HINT which index SQL Server will use.


texts: MySQL InnoDB add new instance to existing cluster

texts:
Server version: 5.7.23-log MySQL Community Server (GPL)

I'm running an InnoDB cluster and trying to add a new node, the cluster is a couple of years old and binlogs are being cleared after 2 weeks.

Adding a new node to a new cluster is straight forward enough because the master has all the binlogs but I can't find anywhere pointing me to instructions on how to add a new node to an existing cluster.

It's in production and I don't want to 'test and see' in case I break something so I'm looking for a definitive set of instructions.

Can anyone assist please?


texts: cant truncate tables with a pk/ fk relationship?

texts:
I have a table called Folder with 3 fields: Id (PK), Name, UserId (FK)

and another table called User with two fields: Id (PK), UserName

i am trying to run this sql:

truncate table Folder
truncate table User

i get the following error

Cannot truncate table 'Folder' because it is being referenced by a FOREIGN KEY constraint.
Cannot truncate table 'user'   because it is being referenced by a FOREIGN KEY constraint.

...even after deleting all the records in both tables i get this. why am i not allowed to truncate my tables?

how can i reset the seeds of the PK's easily without using truncate if there is now way to run a truncate on pk/fk relationship tables?

Edit:
DB diagram added


texts: Oracle: Why is DBW terminating my Instance?

texts:
I was performing routine administrative tasks on an Oracle instance and I was not able to connect to it.

I proceeded to do as follows to reach a diagnosis:

I checked for Oracle processes in memory and saw that the instance was down.
I tried to startup the instance but it never responded to the startup command. It simply stalled. No output or feedback, no matter how long I wait. Only a kill -9 can get me out of it.
I reviewed the alert log and the last message was 3 days ago:

"DBW0: terminating instance due to error 472
Instance terminated by DBW0, pid = 14952"

Questions

What can cause BDW to terminate an instance?
Why I don't get any feedback, whether in the sqlplus console or the alert log when I try to startup the instance.

When I ran strace, I got this:

ERROR: unable to open /dev/log.

I'm running Oracle on SunOS 5.8 Generic_117350-08 sun4u sparc SUNW,Sun-Fire. The RDBMS version is 9.2.0.8.0

EDIT:

I followed advice from both @balazs-papp and @jsapkota about running truss:

When I run truss on the sqlplus the I try to startup the instance, I get this output:

read(0, " s t a r t u p\n", 1024)               = 8
write(9, "\0 U\0\006\0\0\0\0\0038A".., 85)      = 85
read(10, 0x10029A536, 2064)     (sleeping...)
signotifywait()                 (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
door_return(0x00000000, 0, 0x00000000, 0) (sleeping...)

How can I interpret this output? It's totally cryptic to me.


texts: How can a fill factor of 0 or 100 be the same?

texts:
As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth.
I can't correlate how fill factor of 0 and 100 can be the same!
Am I missing something?


texts: Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group

texts:
I have a query that returns some rows having a grouping on a column and dates. I would like to have that query indicate in a column which of each group has the greatest date.
There are a vast number of questions on how to do the "greatest n per group", but those questions only want the row from each group with the greatest value. I want all the rows, and just an indication of which is the greatest.
For example, here is a table:

id
rep
date

1
27058
2020-11-16

2
27058
2020-11-09

3
27058
2020-10-30

4
46029
2020-11-03

I would like to have something like the following:

id
rep
date
greatest

1
27058
2020-11-16
1

2
27058
2020-11-09
0

3
27058
2020-10-30
0

4
46029
2020-11-03
1

I have experimented with some greatest-n-per-group methods to add a "greatest" indicator. But since those attempts are focused on the only the row with the greatest value, when the part of the where clause like "table2.date IS NULL" is removed, extra rows are added with all the join combinations.
Here is an example of such an experiment that does not work. It tries to use the greatest-n-per-group but comments out the IS NULL line:
SELECT DISTINCT m1.usermessageid, m1.repid, m1.sender_role, m1.message, m1.datesent, m2.datesent
FROM usermessage m1
LEFT JOIN usermessage m2 ON m1.principalid = m2.principalid and m1.repid = m2.repid AND m1.datesent < m2.datesent
WHERE m1.principalid = 99831
AND m1.repid IN (21600,99214,27058,97360,92678,96184,55429,46029)
# AND m2.datesent IS NULL
ORDER BY m1.datesent desc;

I would like to avoid having to add an additional query just to get the greatest rows.


texts: How to Configure Always Encrypted using PowerShell

texts: I just want to ask if there is a way to configure always encrypted columns using power shell? like for example i want to encrypt a column in a table with deterministic using powershell codes?


texts: Is the SQL Server IsShutdown property useful to determine whether a database is in a good state?

texts:
My company has a tool that monitors statuses on servers, services, databases, etc. We monitor a number of on-site servers for our customers. One particular simple check performed is to determine whether a SQL Server database is in a 'good' state by querying for the value of certain database properties. The four database properties we monitor are:

IsSuspect
IsOffline
IsEmergencyMode
IsShutdown

This is the query we use:

SELECT name AS [SuspectDB],
  DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
  DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
  ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) AS [Shutdown],
  DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
   OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
   OR (ISNULL(DATABASEPROPERTY(name, N'IsShutdown'), 1) = 1)
   OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)

In testing an upgrade to SQL Server 2008, it seems that quite a few of our databases are returning a 1 (true) value for the IsShutdown property. This was never the case previously with SQL Server 2005. The MSDN documentation for the property simply states "Database encountered a problem at startup".

As far as I can tell, the databases are perfectly fine. They are up, can be queried, etc. No issues.

Does the IsShutdown property really matter for my monitoring purposes, i.e., does it indicate that the database is in a bad state? Or should I just remove it from my query?

NOTE: In talking to one of our resident DBAs, they found that on some of our new SQL Server 2008 databases, the fact that the IsAutoClose property is enabled might have something to do with the reason for these databases having an IsShutdown of true. Disabling IsAutoClose seems to "fix" the IsShutdown being true.


texts: POSTGRESQL REINDEX INDEX Time estimation

texts:
to explain the context, I test postgres with a huge table (I try to have 1TB data) in a docker container for isolation, and I ran my container with a -v parameter in order to mount my dedicated HDD (spinning 1TB). So I ran my ingest process, everything worked fine.

Next, I had to run an other container in which I wanted to ingest only 25M rows to make comparison, BUT I ran my docker container with the same -v parameter ... oups ... so I stopped my container and, big mistake, remove it, so my ingest process has been stopped because the previously was unaccessible, which makes sense I suppose.

So I thought "it's ok, I will restart it an rerun the ingest", which I've done, but I got that error:

psql:/home/inserts24_8MTransac_200_124000_263.sql:124001: ERROR:  XX002:
index "test_table_pkey" contains unexpected zero page at block 17855039
HINT:  Please REINDEX it.

So I run the reindexing command:

TestDB=# REINDEX INDEX test_table_pkey;

But I have almost 6.5 Billions rows, and it seems it takes so much time, I only have a primary key on (id) and an index on two rows (both are integers).
My table contains only 6 rows (One Bigint for the id, Integers or small integers for the rest).

Is it normal the REINDEXING process take a lot of time?

Thanks


texts: What indexed view capabilities does Azure SQL Database have?

texts:
Does Azure SQL Database support indexed views? Does it support automatic indexed view matching without NOEXPAND? Does the answer depend on the pricing tier?

I was unable to find definitive answers to these questions. The feature differences guide does not mention indexed views.

The indexed view documentation does not contain the word "Azure".


texts: Teradata: How to design table to be normalized with many foreign key columns?

texts:
I am designing a table in Teradata with about 30 columns.  These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc.  It is bad design to store the actual string values in the table since this would be an attrocious repeat of data.  Instead, what I want to do is create a primitive lookup table.  This table would hold Daily, Monthly, Weekly and would use Teradata's identity column to derive the primary key.  This primary key would then be stored in the table I am creating as foreign keys.

The table would be designed like this:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
2   Interval     Monthly
3   Interval     Weekly
4   TimeFrame    24x7
5   TimeFrame    8x5

This would work fine for my application since all I need to know is the primitive key value as I populate my web form's dropdown lists.  However, other applications we use will need to either run reports or receive this data through feeds.  Therefore, a view will need to be created that joins this table out to the primitives table so that it can actually return Daily, Monthly, and Weekly.

My concern is performance.

I've never created a table with such a large amount of foreign key fields and am fairly new to Teradata.  Before I go on the long road of figuring this all out the hard way, I'd like any advice I can get on the best way to achieve my goal.


texts: Is CREATE EXTERNAL DATA SOURCE Azure PaaS elastic databases broken?

texts:
I have set up Azure elastic database queries before by using the following on Azure PaaS databases:

On the data source database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<encryption key>';
GO
CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login
WITH IDENTITY = 'SomeUser', SECRET = '<Some password>'
GO

This run, no problems and I can see the credential created when looking at sys.database_scoped_credentials. So it seems the source is fine.

On the destination database:

CREATE EXTERNAL DATA SOURCE Source_AzureDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='<DB location>.database.windows.net',
    DATABASE_NAME='SourceDBName',
    CREDENTIAL= CRED_Azure_Login
);

This used to work a-OK, but I get the following error:

Msg 46516, Level 16, State 26, Line 1
The specified credential cannot be found or the user does not have permission to perform this action.

I am using the admin login in the destination database (I changed the admin login via the Azure Portal Overview page for the database and re logged in, just to be sure). The documentation says you need CONTROL permission on the database, so this shouldn't be an issue. As I have successfully set up the source databases Master Key and credential, linking to it should not be a problem.

Can someone else try this to see if it is just me? Another DBA has reviewed the code and can't find any problems. Feels like Microsoft might have broken this feature - can someone else check they can do it?


texts: How do I prevent a massive SELECT from blocking other statements?

texts:
Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production and here's what sys.dm_exec_requests was showing for that period of time... The longest running query was that heavy SELECT which had PAGEIOLATCH_SH wait type. Next were other queries - most often INSERT statements which had PAGEIOLATCH_EX wait type, all running for dozens of seconds instead of completing promptly. So basically that heavy SELECT alone disrupts other queries.

How do I resolve this? I'm okay with heavy SELECT running slowly but other queries should not be disrupted.


texts: Postgresql Explain Analyze - help: actual time in loops

texts:
I'm trying to interpret an Explain Analyze, but I did not understand this:

According to the Postgresql documentation at: https://www.postgresql.org/docs/9.6/static/using-explain.html

" the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution.
.... Multiply by the loops value to get the total time actually spent in the node"

But look at this case, in which the total query time was 66 minutes.
(Explain Analyze complete and Query at this link: https://sites.google.com/site/eletrolareshop/logo/arqw1-ssd-v3.txt )

What interests me is this section:

  -> Index Scan using idx_l_partkeylineitem000x on lineitem (cost = 0.57..97.65 rows = 26 width = 36)
                  (current time = 23.615..419.113 rows = 30 loops = 26469)
                  Index Cond: (l_partkey = part.p_partkey)

According to the documentation, one should multiply the Actual Time by the number of Loops.
That is: 419.113 ms -> 419.113/1000 = 0.41 seconds * 26469 (loops) = 11093.50 seconds or 184.8 minutes.

But how does this stretch take 184.8 minutes, if the entire query ran in 66 minutes?

Of course I'm making a miscalculation, but if anyone can give me a hint as to how I would calculate this time.
What I need to know is the time spent go through the idx_l_partkeylineitem000x index, remembering that I did an Explain Analyze which is theoretically the actual time spent and not an estimate
as happens with the simple Explain .

thank you and best regards
[] 's Neto


texts: How do I find all tables in a DB that have no explicit primary key?

texts:
A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as a clustered index, and a non-clustered surrogate index, like an identity column.

How do I find all tables in a DB without a primary key defined? I have 245 tables in this DB: manual inspection is grossly inefficient.


texts: HSQL DB grows large and is full of zeroes

texts:
I have an application whose HSQL database grows awfully large for some users, but not others. When I received one example where this problem happenned, I discovered that the database is nearly empty - it's almost 16GB of zeroes and only a couple tens of megabytes of real data. Upon performing CHECKPOINT DEFRAG, it shrinks to its real, small size.

Unfortunately, I'm not very familiar with this database engine and my online searches have produced little useful information. One interesting thing was that there is an option to automatically perform the defragmentation upon reaching a certain size, which I didn't use. However, I would expect the DB to reuse dead rows and not keep them around, and definitely not zeroed out as this must have a bad effect on performance.

Why does the database keep producing so much empty space, which is only interrupted by data here and there? How can I find out what's happening?


texts: Stored procedure for selecting table rows from a date range used to populate two other tables

texts:
I have this two tables shipments_docs and dict_shops:

CREATE TABLE shipment_docs (
  doc_id INT PRIMARY KEY,
  doc_num VARCHAR(20) NOT NULL,

  doc_date DATETIME NOT NULL,

  shop_id INT NOT NULL

);
GO
CREATE TABLE dict_shops (

  shop_id INT PRIMARY KEY,

  shop_name VARCHAR(30) NOT NULL,

  town_id INT NULL,

  shop_address VARCHAR(100) NOT NULL

);
GO

And I need to populate them with values from the third table (dbo.shipment_data) that fall into the date range.

CREATE TABLE dbo.shipment_data (

  doc_num VARCHAR(20) NOT NULL,    -- номер документа

  doc_date DATETIME NOT NULL,      -- дата документа

  shop_name VARCHAR(30) NOT NULL,  -- наименование торговой точки

  shop_address VARCHAR(100) NOT NULL  -- почтовый адрес торговой точки

);

I need to implement stored procedure to populate the shipment_docs and dict_shops tables. The procedure should take two parameters - the start date and the end date - which should limit the loaded data set by the doc_date field.

CREATE PROCEDURE FillTables
@DateFrom DATETIME,
@DateTo DATETIME,
AS
BEGIN
DECLARE @flag INT;
SET @flag = 1;
WITH theDates AS
        (SELECT @DateFrom as theDate
         UNION ALL
         SELECT DATEADD(day, 1, theDate)
           FROM theDates
          WHERE theDate < @DateTo
        )
..........

After reading Google, I realized that in this case it is necessary to use the wif construction. But I can’t understand what the whole query should look like.


texts: Optimize postgresql query with multiple subqueries

texts:
I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high).
Table candles contains 50 millions rows.

CREATE TABLE candles (
    timestamp BIGINT NOT NULL,
    instrument_id INTEGER NOT NULL,
    notional_usd BIGINT NOT NULL,
    CONSTRAINT candles_pk PRIMARY KEY (timestamp, instrument_id)
);

Note : timestamp cannot be changed from BIGINT to TIMESTAMP for compatibility issue

Query :

EXPLAIN ANALYSE
SELECT
    extract(hour from to_timestamp(timestamp/1000)) as "time",
    extract(hour from to_timestamp(timestamp/1000))::text as metric,
    SUM(notional_usd)/t.max_notional_usd*100 AS notional_usd_percent
    FROM candles,
    (
        SELECT
          MAX(sumed.notional_usd) AS max_notional_usd
        FROM (
          SELECT
            extract(hour from to_timestamp(timestamp/1000)) as "time",
            SUM(notional_usd) AS notional_usd
          FROM candles
          GROUP BY "time"
        ) AS sumed
    ) AS t
GROUP BY "time", t.max_notional_usd

Result :

"GroupAggregate  (cost=15600527.41..16047921.77 rows=220656 width=104) (actual time=185056.051..227426.091 rows=24 loops=1)"
"  Group Key: (date_part('hour'::text, to_timestamp(((candles."timestamp" / 1000))::double precision))), (max((sum(candles_1.notional_usd))))"
"  ->  Sort  (cost=15600527.41..15710031.53 rows=43801648 width=56) (actual time=183186.442..205920.751 rows=43801648 loops=1)"
"        Sort Key: (date_part('hour'::text, to_timestamp(((candles."timestamp" / 1000))::double precision))), (max((sum(candles_1.notional_usd))))"
"        Sort Method: external merge  Disk: 2143000kB"
"        ->  Nested Loop  (cost=3815240.76..5549731.22 rows=43801648 width=56) (actual time=31596.456..86215.017 rows=43801648 loops=1)"
"              ->  Aggregate  (cost=3815240.76..3815240.77 rows=1 width=32) (actual time=31596.429..31596.430 rows=1 loops=1)"
"                    ->  Finalize GroupAggregate  (cost=3611424.70..3812482.56 rows=220656 width=40) (actual time=20496.568..31596.383 rows=24 loops=1)"
"                          Group Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                          ->  Gather Merge  (cost=3611424.70..3804207.96 rows=441312 width=40) (actual time=20022.571..31596.259 rows=72 loops=1)"
"                                Workers Planned: 2"
"                                Workers Launched: 2"
"                                ->  Partial GroupAggregate  (cost=3610424.67..3752269.58 rows=220656 width=40) (actual time=19835.282..30720.177 rows=24 loops=3)"
"                                      Group Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                                      ->  Sort  (cost=3610424.67..3656051.39 rows=18250687 width=16) (actual time=19370.253..25047.717 rows=14600549 loops=3)"
"                                            Sort Key: (date_part('hour'::text, to_timestamp(((candles_1."timestamp" / 1000))::double precision)))"
"                                            Sort Method: external merge  Disk: 371464kB"
"                                            Worker 0:  Sort Method: external merge  Disk: 377288kB"
"                                            Worker 1:  Sort Method: external merge  Disk: 365720kB"
"                                            ->  Parallel Seq Scan on candles candles_1  (cost=0.00..785454.74 rows=18250687 width=16) (actual time=0.029..8526.498 rows=14600549 loops=3)"
"              ->  Seq Scan on candles  (cost=0.00..858457.48 rows=43801648 width=16) (actual time=0.017..16963.830 rows=43801648 loops=1)"
"Planning Time: 0.154 ms"
"Execution Time: 228151.276 ms"

How could I optimize this query ?
This query 'only' takes 37s without the embeded selects. What I basically want is to divide SUM(notional_usd) for each hour by the max value. Is there a way I could do this without the embeded select ?


texts: Compare multiple field to single IN query

texts:
I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg:

SELECT unit
FROM trips
WHERE owner1,owner2,owner3 in (select id from login)

Can this be done in PostgreSQL?

Using version 9.3.4


texts: Is there a way to get slow-query-log-like profiling information on a single query?

texts:
Specifically, I'm looking for this information:

# Query_time: 0.011922  Lock_time: 0.000088  Rows_sent: 107  Rows_examined: 663  Rows_affected: 0  Rows_read: 107
# Bytes_sent: 4152  Tmp_tables: 3  Tmp_disk_tables: 3  Tmp_table_sizes: 259964

on queries I run in the console.

We're running Percona 5.5.


texts: How to copy a database in sharded cluster

texts:
We have a sharded MongoDB cluster, and we want to the copy current collection.

Is there a special process to do "copy collection" from a sharded cluster?


texts: SSAS Model Refresh - Not enough memory to complete this operation Error

texts:
We have started encountering an issue regarding the refresh of our tabular SSAS model.

The tabular SSAS model has 38 tables within it.

This process has been running without issue for over a year, however for around a month now, we have not been able to sucessfully process the tables within the model.

If i access the SSAS database > Right Click > Process Database > Select the mode to Process Default followed by OK, this is when the problem occurs.

It will sit there for around 5 minutes before failing with the error messsage:

Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available.

If i try and 'process' the tables individually, i recieve the same error message too.

I have looked into the memory settings for SSAS, within the advanced window and have reset the values to their defaults. So the key values (as im aware of) are currently:

The server has been rebooted several times, we still have the same problem.

Environment Details:

Windows Server 2016 Datacenter

SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64)

SSAS Version: 14.0.223.1

Server Mode: Tabular

Server Memory: 64Gb

SQL Server Assigned Memory: 28Gb

I have ready multiple articles online regarding these sorts of problems, however nothing seems relevant / useful so far.

Any guidance / assistance would be greatly appreciated.

Disclaimer: I am not a BI / SSAS guy. Im just a DBA who has been given this problem to look at, so forgive me if i dont quite explain this correctly.


texts: Speed up large clustered index creation?

texts:
I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB.

And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit windows server 2008 R2 enterprise.

I run

create unique clustered index MyTable_IXC on tblFactFoo(barKey) on [PRIMARY]

But it took more than 6 hours (actually, it reported an error of duplicated key after 6 hours).

When running it, the cpu was less than 10%, and disk IO was less than 20M/s, normally about 15M/s, I wonder how to improve the performance of creating a clustered index with such powerful hardware.


texts: Can I configure MySQL replication to break when I run a "drop database" or "drop table" command on the master?

texts:
Basically, what the title says.  This would enhance the failover quality of our systems, becuase we have run into issues of people thinking that they were in a test environment running drop statements on the master, which then (obviously) resulted in data loss.

Instead, I was wondering if there was a db option to just have replication refuse to run the drop table command, and have replication stop running at that point.  (assume statement based replication for now).  MySQLs FAQs seem to indicate that the answer to this is 'no'.


texts: Measuring the relative performance of queries given the assertion that "cost" isn't reliable

texts:
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

Source

Does this make room for a more reliable metric we can use to measure the relative performance (CPU, I/O, or time) of similar queries which are designed to have the same end goal?


texts: Really strange behavior using variables in MySql query

texts:
I was answering a Question on Stack Overflow forum and the OP notice a really strange behavior that I could reproduce using the SQLFiddle site

So lets go to it.

We have this setup:

CREATE TABLE inventory
    (`animal` varchar(6), `date` date, `quantity` int);
INSERT INTO inventory
    (`animal`, `date`, `quantity`)
VALUES
    ('dog', '2015-01-01', 400),
    ('cat', '2015-01-01', 300),
    ('dog', '2015-01-02', 402),
    ('rabbit', '2015-01-01', 500),
    ('cat', '2015-01-02', 304),
    ('rabbit', '2015-01-02', 508),
       ('rabbit', '2015-01-03', 524),
       ('rabbit', '2015-01-04', 556),
       ('rabbit', '2015-01-05', 620),
    ('rabbit', '2015-01-06', 748);

The requirement of the OP is to have this result:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

So I did it as you can see it here: http://sqlfiddle.com/#!9/c77d8/182

It is working fine, but the OP said that on his side he was experiencing a strange behavior, the very first time that he runs the SQL it returns all null for the quantity_diff column and on the second time it returns -248 for the last quantity_diff.

So I asked what was that he did different and the only thing that I noticed is the MySql version. In my answer I've create the setup on the 5.6 version (5.6.21 on the above fiddle) and on his version he created the setup on the 5.5 (5.5.44-0ubuntu0.14.04.1) his fiddle

The more strange thing is that I've tested the same setup here on my machine with mysql version 5.6.21 (windows 7) and it behaves exactly as the user mentioned with the MySql Worckbench as client, but I could solve the problem adding the variable definition prior to the query execution as this:

set @qt:=null;
select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff
from ( select i.animal,
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit'
       order by `date` desc
     ) tab
order by `date`;

So Is this some weird behavior? Or some known bug? Or (most probably) a server configuration regarding the use of variables @something in a query?


texts: What queries are supported by MySQL but not PostgreSQL?

texts:
In process of migrating to PostgreSQL and am looking for a list of types of queries I must change (or some online query converter should one exist).  What originally prompted this is my attempt to execute LEFT OUTER JOIN t ON t.id=t1.id || t.id=t2.id || t.id=t3.id which works with MySQL but not PostgreSQL (turns out I must use OR instead of || and I expect the same goes for &&).

https://dba.stackexchange.com/a/60254/95143 primarily lists PostgreSQL features which are not supported by MySQL and may be out of date (last updated 3 years ago), however, might list some still relevent:

There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.
Things that MySQL has (but not Postgres) are clustered indexes, GROUP BY .. ROLLUP and the Event (scheduler). – (From ypercube's comment)

Many of these I don't care about, however, I will definitely need to address ON DUPLICATE KEY UPDATE and take a close look at PostgreSQL's reserved words.  What other queries or features would one need to change?


texts: Does column store index reduce data space ? How this is possible?

texts: I was testing for performance of one of my queries. I was trying different indexes. When I applied a column-store index on my table, the table size reduced by 70-80 %. How is this possible?


texts:
I have a laptop with Centos 7. I installed Oracle 12C database on it.The oracle user is oracle and the sid is orcl.
How can a user that is not oracle user run the sqlplus program directly ?

The .bash_profile for the oracle user is

if [ -f ~/.bashrc ] ; then
    . ~/.bashrc
fi

export TMP
export TMPDIR=$TMP
export ORACLE_HOSTNAME=servera.localdomain
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=orcl
export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=$PATH:$HOME/. local/bin:$HOME/bin
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

question: Do I need to have these variable exports for every user in their .bash_profile file ?


texts:
After alter function execution paln did not change.

Alter FUNCTION dbo.F_New
(
    @i integer
)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style

 RETURN CONVERT(datetime, '19000101', 112);
END;
GO


texts: Select data from a table and update a second table using most recent value

texts:
I have two table: Stampings and Registries.

Stampings is populate from a program that read a CSV file and do an insert into the table. Some field into the table are: name, surname, card-code and employee-freshman.

Registries contains name, surname, card-code and employee-freshman.

Now, I need a control that from Stampings select name, surname, card-code and employee-freshman and comparate with data present into Registries using card-code like unique field.

If value from Stampings and Registries not match I want to update the record in Registries with the new value using date like where cause. The update must do taking the last inserted record into Stampings.

This because a card can be from an user, but at any time it could be transferred to another person.

Can someone help me?

This is my schema:

Stampings
(
ID INT
Date DATE
Hour VARCHAR
Card-Code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

Registries
(
ID INT,
Card-code VARCHAR
Name VARCHAR
Surname VARCHAR
employee-freshman VARCHAR
);

I used C# tag because here below I report the code I'm using.

Tell me if you think that logic is correct to approach.

using (SqlCommand selezionaCodiciTessera = new SqlCommand("SELECT Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman FROM Stampings INNER JOIN Registries ON Stampings.Card-code = Registries.Card-code WHERE Stampings.Date = (SELECT MAX(Date) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) AND Stampings.Hour = (SELECT MAX(Hour) FROM Stampings WHERE Stampings.Card-code = Registries.Card-code) GROUP BY Stampings.Card-code, Stampings.Surname, Stampings.Name, Stampings.employee-freshman ORDER BY Stampings.Card-code;", connessione))
{
    SqlDataReader reader = selezionaCodiciTessera.ExecuteReader();

    while (reader.Read())
    {
        using (SqlConnection connessione2 = new SqlConnection(connString))
        {
            connessione2.Open();

            string query = "";

            if (reader[1].ToString().Contains("'"))
            {
                string modifica = reader[1].ToString().Replace("'", "''");

                query = "UPDATE Registries SET Surname = '" + modifica + "', Name = '" + reader[2].ToString() + "', employee-freshman = '" + reader[3].ToString() + "' WHERE Card-code = '" + reader[0].ToString() + "';";

                using (SqlCommand aggiornaOinsersci = new SqlCommand(query, connessione2))
                {
                    aggiornaOinsersci.CommandTimeout = 180;

                    try
                    {
                        aggiornaOinsersci.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        LOG("Timeout nell'esecuzione della query. " + e.ToString());
                    }
                }
            }
        }
    }
}


texts: Replication/log shipping to a standby site

texts:
I need to replicate a SQL Server database on my standby site. Regarding Log Shipping, I have the following concerns:

Is there any data type or primary key dependency exists if I use log shipping?
Why I have to use network folders/directories. Why can I not use local folders to capture log files?
What about the password between the two databases i.e primary and standby as primary will connect to standby?
Is there any method whereby I can open my standby database in read only mode and logs are applied without interrupting or disconnecting my users?
I have a very large database i.e of 45 TB. So for disaster recovery solution what method do you suggest?
Please also mention the difference between "Log shipping" & "Always ON".