texts: Insert all dates from a month into a table

texts:
I'm confused. How to get all dates from a month and insert them into a table in a database?

I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table attendance it will generate one row for each date.

I have table period

period | start_period | end_period
April  |   2016-04-01 | 2016-04-30

and I have table attendance

attendance_date  | check_in | check out
2016-04-01       |     null |      null
..........
2016-04-30       |     null |      null

How to get all dates between 01-04-2016 and 30-04-2016 and insert them into table attendance?


texts: Install Oracle Weblogic: Unsupported major.minor version 52.0

texts:
I tried to install Oracle Weblogic on Redhat Linux 8

using command

java -jar fmw_12.2.1.4.0_wls_quick.jar

but got an error:

Unsupported major.minor version 52.0

Java version

java version "1.7.0_80"

Then I switched to a higher java version, which is:

openjdk version "1.8.0_232"

Then I got an error:

The OpenJDK JVM is not supported on this platform

What is wrong with that?


texts: Restore SQL Server database from .sql files

texts:
So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git

The problem is they're saved as .sql files.

Is there anyway we can batch restore our schema from directories full of these files?

I've looked around and I can only find tutorials for restoring from .bak files or .mdf's. This isn't the lazy man's way out- I just need to find a solution ASAP. Any advice or resources/ anything at all would be greatly appreciated.

Thanks Interwebs,

Dylan


texts: How likely is fragmentation on a table with 40000 products likely to affect performance

texts:
So database administration is not a strong point of mine, so I have a few questions.

This all relates to the performance of a website I'm looking at, and have spent a fair bit of time researching/diagnosing.

There are two sites, a test site and a live site.
I have noticed page load times are much longer on the live site as opposed to the test site.
The specs of the test site are really low - VPS 2 Virtual processors with 14GB RAM and SQL + website running on the same VPS
The specs of the live website are 4 dedicated servers all with 8 virtual processors and 16GB RAM
The live site uses a separate dedicated server for SQL with 16 virtual processors and 112GB RAM
The website averages 120 concurrent users average at any one time but can fluctuate from 70 to 150 and when we send out an email campaign it can hit 400. When we hit 400 the cpus as you would expect can hit 100% depending on what actions users are doing on the website, but they hold up fairly well same with the RAM.

I would expect the live website to actually perform faster than the test website, but this is not the case.
I have looked through the code but I strongly believe it's due to the database.
The database size of the live website is over 50GB when backed up, and the test site is 4GB.

Both databases are quite fragmented, and I'm wondering how likely the fragmentation will affect performance?

Also because the live website is many times larger than the test site, will high fragmentation affect the performance of the live site much more than the test site?

Here is a capture of one of the indexes in the products table.

Because I have never dealt with this before what are some tips around rebuilding/refreshing indexes.

Should I put the website into maintenance mode so no-one can interact with tables while I'm rebuilding/refreshing indexes?

How long would it take on a table with 12 indexes and 40000 products?

Thanks


texts:
I have a PostgreSQL 9.3 database with the PostGIS extension backing a web application and GeoServer. I have created three users.

A read only user for accessing the data in the reference data schema.

A read/write user for accessing and updating data in the schema that holds the application state.

A read/write/create user for the mapping data being accessed by GeoServer.

The first two work fine. The third one I'm having an issue with. Logged in as the user GeoServer can create new tables just fine. But subsequently cannot read from them. I have to go in specifically and manually apply the permissions. This is untenable for the application. It doesn't make a lot of sense to me that a user could create a table they can't read from. So I figure there is a non-obvious permission setting I missed. My Google searching led me to plenty of instructions for oddities of setting up a read only user, but not for this.


texts: unable to set kerberos authentication in mssql 2014 express

texts:
Hello I've recently installed MS SQL 2014 Express in MS Server 2016 Essential
I wanted to use Kerberos authentication so I've run through many pages but whenever I'm trying authentication always says NTLM.
I have only one server where I'm running AD and SQL and this is physical one.

What was done:

service account security settings:

granted rights to read/write public information

service account delegation -> for specific services use Kerberos only:

all listed as MSsqlsvc Service Type

SQL Server Configuration Manager

Network congfiguration -> Enable TCP/IP
Port changed from dynamic to static one

Component Services -> Local DTC

Enabled Network DTC access
Allow remote clients and admins
Allow Inbound and Outbound
Mutual authentication required

    C:\Users\sql_svc>setspn -l SOMEDOMAIN\sql_svc
        Registered ServicePrincipalNames for CN=sql_svc,CN=Users,DC=SOMEDOMAIN,DC=local:
        MSSQLSvc/SERVER_NAME.SOMEDOMAIN.local:58568
        MSSQLSvc/SERVER_NAME.SOMEDOMAIN.local:SQL_INSTANCE_NAME

Kerberos Configuration Manager says status GOOD for SPN and 'No Obvious delegation issues' in Delegation tab

I'm running out of ideas. Any ideas much appreciated

query used to check connection type:

select auth_scheme from sys.dm_exec_connections where session_id = @@spid


texts: Add SQL Server Agent to existing SQL Installation

texts:
If there is a SQL server installation that is physically missing the SQL Server Agent is there a way to add it without reinstalling SQL Server? Will installing a service pack do it?

Today I was asked to look at a SQL server another department had installed. It's SQL 2016 SP1 Standard Edition. SSMS shows SQL Agent, but there is no SQL Agent service in services or in Configuration Manager. And no mention of SQL Agent in the registry.

In the installation path I see the various SQL Agent files and I tried to register the service via the SC command but it didn't work. The service is there but it won't start.

Short of detaching the databases, uninstalling and reinstalling, is there anything else that can be done to get it working?


texts: creating an inventory management system app

texts:
for creating a inventory management app , i designed this database i want to store the product bought in the inventory as a positive value and the product sold as a negative value o nthe inventory so i can know the quantity remaining here is the database design

its first time creating a database , does it answer my need ?


texts:
Suppose I have a table A with columns id:int, kpi:int, created_at:datetime. And suppose I would need to fire the following query:
SELECT SUM(kpi) FROM A WHERE (created_at > X AND created_at < Y)

This query would make a full scan. But it can be optimised with a data structure segment tree. It takes O(nlog(n)) to build and O(log(n)) to query (instead of O(n) in case of full scan).
The question is, is there any index or plugin for Postgres (or other SQL Database) that does this?


texts: Azure SQL Database "Login failed for user" in application, but works fine in SSMS

texts:
I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me.

I created a database called Classifier.  I added my IP to the firewall rules so I could connect to the Azure db server from SSMS on my workstation.  Once I was able to get connected via SSMS for administration, I tried adding a user with a password to the database, like this:

CREATE USER classifier WITH PASSWORD='thepassword'

I also added this user to the data writer and reader roles:

exec sp_addrolemember 'db_datawriter', 'classifier'
exec sp_addrolemember 'db_datareader', 'classifier'

After this, I'm able to connect to the database with these credentials from SSMS:

But this is where things go awry: I've tried several different connection string incantations and can't seem to get connected in a web app I'm working on.  It didn't work in the Azure environment, so I'm running on localhost with a connection string to the Azure database, and it just won't connect.  Here's the connection string I'm using at the moment:

<add name="Classifier" connectionString="Data Source=xxxxxxx.database.secure.windows.net;Initial Catalog=Classifier;User ID=classifier;Password=xxxxxxxxxxxxx;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient"/>

I've tried resetting the password (via SSMS) for the user and then updating the connection string; I also double-checked the password by copying it right out of this connection string and into the connect dialog in SSMS to make sure I didn't have a typo of some kind there.

I enabled auditing in the Azure db server hoping to get some details as to why it's failing, but all I get is this:

And this is where I'm stuck.  Most of what I've been able to find by way of documentation or blogs indicate that the thing to do is look at SQL Server logs to see what the real error state is which would indicate more narrowly the nature of the failure, but since I'm dealing with Azure there's no way to do that (as far as I know).

What could cause the application to fail where SSMS (and LinqPad and Visual Studio Server Explorer, incidentally) succeeds?


texts: For multiple mysql instance , fresh download required when one exist?

texts:
i need multiple instances of Mysql on same server.
I had been through many links , but i confused here :

What i have : MySQL 5.6 is already installed on my windows server A.
Installed at : "C:\Program Files (x86)\MySQL\MySQL_Server_5.6\"

Problem :   For multiple instances , do i need to download and install new mysql(either same or higher version) on server A ?

OR
  should i just copy existing my.conf file in new folder and then change its port number and then done.

My own analysis goes on fresh installation , but please confirm .


texts:
Is there a way to disable multiple sql logins based on a set of users in a table? For example:

ALTER LOGIN (Select userid from tbl_user) DISABLE;


texts: argument 'and' incorrect, sum values of attributes

texts:
excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries? I tried with 'UNION' and with + but it gives me error.

case when(--for example 8=8
/*8=*/      (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole') =(
/*=8*/       ((
    (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole')
     UNION(select count(*) from voto where new.data=voto.data and
voto_palese='contrario') UNION
     (select count(*) from voto where new.data=voto.data and
voto_palese='astenuto'))/2)+1) and
      (select count(*) from voto where new.data=voto.data
 and(voto.ddl=new.ddl or voto.mozione=new.mozione))
           )
 then new.esito='approvato'
 else new.esito='non approvato'
 end, --esito,

ERRORE:  l'argomento di AND deve essere di tipo booleano, non bigint
LINE 34:           (select count(*) from voto where new.data=voto.dat...
               ^
QUERY:  insert into public.votazione(
       mozione,
       ddl,
       codice,
       favorevoli,
       votanti,
       contrari,
       astenuti,
       esito,
       tipo_assemblea,
       seduta_numero,
       data,
       assenti,
       missione)
 values((select codice from mozione where codice=new.mozione and
 mozione.data=new.data),--mozione,
 (select titolo from ddl where titolo=new.ddl), --ddl,
 (select codice from ddl where new.ddl=ddl.titolo), --codice,
 (select count(*) from voto where new.data=voto.data and
                               voto_palese='favorevole'), --favorevoli,
 ((select count(*) from voto where new.data=voto.data and
                               voto_palese='favorevole')
 UNION(select count(*) from voto where new.data=voto.data and
                               voto_palese='contrario') UNION
       (select count(*) from voto where new.data=voto.data and
                               voto_palese='astenuto')), --votanti,
 (select voto_palese from voto where data=new.data and
 voto_palese='contrario'), --contrari,
 (select voto_palese from voto where data=new.data and
  voto_palese='astenuto'), -- astenuti,
 case when(--for example 8=8
/*8=*/      (select count(*) from voto where new.data=voto.data and
voto_palese='favorevole') =(
 /*=8*/       ((
    (select count(*) from voto where new.data=voto.data and
 voto_palese='favorevole')
     UNION(select count(*) from voto where new.data=voto.data and
 voto_palese='contrario') UNION
     (select count(*) from voto where new.data=voto.data and
 voto_palese='astenuto'))/2)+1) and
      (select count(*) from voto where new.data=voto.data
 and(voto.ddl=new.ddl or voto.mozione=new.mozione))
           )
 then new.esito='approvato'
 else new.esito='non approvato'
 end, --esito,
 (select tipo_assemblea from ordine_del_giorno_ass_cam_e_sen where
 data=voto.data), --tipo_assemblea,
 (select seduta_numero from ordine_del_giorno_ass_cam_e_sen where
 data=voto.data), --seduta_numero,
 (SELECT data from ordine_del_giorno_ass_cam_e_sen where
   new.tipo_assemblea=ordine_del_giorno_ass_cam_e_sen.tipo_assemblea and
 ordine_del_giorno_ass_cam_e_sen.data=voto.data), --data,
 (select count(*) from timbratura_giornaliera where presenze='Assente' and
 new.data=timbratura_giornaliera.data), --assenti,
 (select count(*) from timbratura_giornaliera where presenze='In missione'
 and new.data=timbratura_giornaliera.data) -- missione
 )
  CONTEXT:  funzione PL/pgSQL inserimento_votazione() riga 4 a istruzione SQL

  ********** Error **********

  ERRORE: l'argomento di AND deve essere di tipo booleano, non bigint
   SQL state: 42804
  Context: funzione PL/pgSQL inserimento_votazione() riga 4 a istruzione SQL


texts: Database Model for Various Type of Sensors

texts:
I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this?

-

Option 1 : should I integrate all sensors into a single database like this

Table Sensor
---
SensorID
SensorType
SensorDesc

Table SensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue

-

Option 2 : Or should I put each sensor type into separate table?

Table TemperatureSensor
---
SensorID
SensorDesc

Table TemperatureSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (double)

Table MotionSensor
---
SensorID
SensorDesc

Table MotionSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (integer)

Considering that various types of sensor tables will have similar fields but potentially different reading data types (some of them are integer and some of them are doubles, etc). Is it logical to separate the tables for each sensors? Or keep using a single table for every type of sensors?

Which one is the best practice to implement such database if there might be various types of sensors to be added in the future?
Which one can be better optimized for performance?

Thank you


texts: renaming a sql server with installed named instances

texts:
We have a hosted server that is named, by the hosting company, as something like WinEnt99. We would like to rename it to something more meaning like OurMonitor1.

We have already installed a few instances of SQL Server 2008 R2.

Is there anything that we need to (re)configure in SQL Server / SSMS ?

I have an application that was reading the database settings from the registry, and connecting the database fine. Now, it won't connect to the database. The only thing that I can think of is that we renamed the server last week sometime.

EDIT 02 Oct 2012:-
The Server is being renamed. The named instances are not.

I've run

sp_helpserver
select @@servername

Which showed the old server name,
and

sp_dropserver 'Old Server Name\Instance_name'
go
sp_addserver 'New Server Name\Instance Name','local'
go

Then confirmed the details in

sp_helpserver
select @@servername


texts:
Very basic questions.
I have 3 tables, very obvious whats the use of them:

--------
CONTACT
--------
ID (SERIAL) - PK
NAME (VARCHAR)

---------------
CONTACT_DETAILS
----------------
ID (SERIAL) - PK
ID_CONTACT (INT)
ADDRESS (VARCHAR)
BIRTH (DATE)

--------
SALES
--------
ID (SERIAL) - PK
ID_CONTACT) (INT)
AMOUNT (NUMERIC)
DATE_PAID (DATE)

1) Should I create a Foreign Key between CONTACT and CONTACT_DETAILS?
If yes, should be CONTACT_DETAILS (ID_CONTACT) ---> CONTACT (ID) or inverse? And you recommend that its cascade?

2) Same question, should I creat a Foreign Key between SALES table and CONTACT?

3) It is a good pract every table have an "ID" serial column, so its easy to identify or there's no need?


texts: Is using indexed view with no expand on standard edition a must?

texts:
I have recently read the text below in 70-762 Developing SQL Databases (p. 67):

  The first benefit of using an indexed view is that when you use it
  Enterprise Edition of SQL Server, it uses the stored data in the index
  structure. For Standard Edition, it uses the code of the query unless
  you use a NOEXPAND table hint, in which case it uses the clustered
  index representation.

and since we have migrated from Enterprise to Standard (using SQL Server 2016 SP1) and have indexed view, I decide to check how this is affecting the performance. So, I executed the queries below:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view]
WITH (NOEXPAND);

and:

DBCC DROPCLEANBUFFERS;

SET STATISTICS IO ON;

SELECT *
FROM [dbo].[vw_my_view];

and the second query was slower and the view definition was expanded, while in the first case, the clustered index scan was used. In this article is said, that NOEXPAND can lead to better performance even on Enterprise Edition.

I am wondering, having the quote above, how indexed view is handled in Enterprise Edition, can we conclude that we should always used WITH (NOEXAPND) in Standard Edition to get the similar behavior?

Why the behavior is not the same in all editions as we have free workaround?


texts: AlwaysOn High Availability - not always kicking in

texts:
We currently have a set up of two nodes A and B with AlwaysOn High Availability.
When restarting node A, we can connect with the listener name 'xyz' to our SQL Server instance. In this scenario, B will shift to primary and A to secondary. Everything is transparent, and connecting to 'xyz' works fine.

Now I've tried to test the scenario by just switching off the SQL Server service on the primary node, rather than restarting the whole node. (Side note: as a consequence, the SQL Server Agent stops as well)

When doing so, I am NOT able to connect to 'xyz' (listener name) via the management studio. I can only connect to 'B' directly. When automatic failover tries to take place, I am left with the following:

Is this normal? Does failing over occur by simply checking the node's status rather than the SQL service of that node?


texts:
I'm trying to recursively loop through and return all child_id's that have the root element of 9.

The structure:

->9
->->8
->->->17
->->22
->->->11

Parent Child Link Table:

+----+-----------+----------+
| id | parent_id | child_id |
+----+-----------+----------+
|  1 |         9 |        8 |
|  2 |         8 |       17 |
|  3 |         8 |       33 |
|  4 |         8 |       18 |
|  5 |         9 |       22 |
|  6 |        22 |       11 |
|  7 |        22 |        4 |
|  8 |         3 |        5 |
+----+-----------+----------+

Procedure (so far):

BEGIN

DECLARE x INT(11)

SET x = 0;
SET @elements = "";
SET @node = _root_; -- 9
SET @child_count = count_children(@node) -- function returning the child count of @node;
SET @children = get_children(@node); -- function returning the child id's of @node

-- check IF node has children
WHILE x <= @child_count DO
    SET @elements = CONCAT(@elements,x,',');
    SET x = x + 1;
END WHILE
SELECT @elements;

END

Desired Output: [8,17,33,18,22,11,4]

Question: How can I modify my procedure to be able to return all child_id's of the parent?


texts: Basic ERD question: how many entities to use

texts:
Say there are Buyers, Sellers, and Reviewers, all have common attributes to them such as:

name
address
login-info (username, password, email)

and all three have abilities unique to their roles.

e.g. buyer can buy, seller can sell, reviewer can review

Assuming a person can be only one of the three roles, what is the best way to design an ERD for this scenario?

Would having 3 separate entities for each role + an entity for LoginInfo having a relationship with all 3 of role entities be good?

If so, would it be redundant to have name, address in all 3 role entities?


texts:
I actually built some "Custom" Monitoring tool to track information from the DMV Tables and store them in some kind of DataWarehouse I'm taking snapshots each 5minutes.

the data I'm retrieving is:

Database:

File Latency (ldf and mdf)
File Sizes and use
Transactions
Log Growths and Flushes
Database CPU used
Database Buffer MB used

SERVER:

SQL CPU / IDLE PROCESS / OTHER PROCESSES
RAM (TARGET/USED)
Page Life Expectancy
Cache Hit Ratio
User Connections / Database Connections

LOCKWAITS:

General Locks/DeadLocks/Waits information (Network... Io Latch...)

QUERIES:

In this part, I'm a bit lost... I would like to be able to detect problematic queries on my server and to do so I need to store historical information as the information on some of the DMVs is cumulative so if I want to some averages like for example execution_count I need to snapshot them.
I don't know what Key KPIS and threshold should a query reach to be considered a problematic query...

Also, I don't know if I should monitor more KPIs in order to detect CPU pressure/ RAM pressure / Disk pressure.

Any help on improving it will be appreciated!


texts: 10.3.9-MariaDB can not query equal operator with special character

texts:
I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?


texts: Mysql query to find last entry for each of the last 7 days

texts:
I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be able to extract the data for the end of each of the last 7 days including the most recent log today. I have two different queries that almost achieve this but not quite:

SELECT *
FROM WaterDepth
WHERE (date >= DATE(NOW()) - INTERVAL 7 DAY) AND (HOUR(date)+MINUTE(date) = 68)
ORDER BY date ASC LIMIT 7

This captures the last 7 days at 23:45, but misses any days that don't have an entry at precisely 23:45 and misses today.

SELECT a1.*
FROM WaterDepth a1
INNER JOIN (SELECT max(date) as max
            FROM WaterDepth
            GROUP BY date(date)) a2 ON a1.date = a2.max

This captures the last entry for each day regardless of what time that is and includes today, but I'm not sure how to limit it to the last seven days. So I guess I'm looking a blend of the two.

Thanks in advance


texts: How to create a unique index with only certain values required to be unique?

texts:
I have a list of codes being stored in a table, and some codes should be unique, others can be repeated. The codes should be unique-scoped to another field, product_id.

Assuming the code 11 should only be allowed once per product_id, and other codes are allowed to repeat, the table would look like:

product_id     code
1              11   # Needs to be unique for product_id 1
1              222
1              222
1              333
2              11   # Needs to be unique for product_id 2
2              222
2              444

With MySQL, I took advantage of the fact that you can have multiple NULL values in a unique index, so by adding a "tie breaker"(?) field, ucode , I was able to hack together a solution:

product_id  code   ucode
1           11     1    # Code 11 needed to be unique, so 1 for ucode
1           222    NULL # Code 222 can be repeated, so NULL for ucode
1           222    NULL
1           333    NULL
2           11     1
2           222    NULL
2           444    NULL

Unique index was then made on [product_id, code, ucode].
For unique codes, the ucode field was set to 1, otherwise NULL.

This works, but feels very kludgy. Is there a better way I can do this?

(I am using MySQL)


texts: I want to exclude rows from an SSRS report based upon a field value and display a message if, as a result of filtering, there are no results

texts:
I have a report where I want to exclude most of the rows based upon a field value. That part is not so hard. The value that I am filtering out is "OK". so in the end the report only displays rows that contain warnings. Typically only one or two rows out of 50-60.
I have been using the expression:
=IIF(Fields!ALERT_VALUE.Value = "OK", True, False) for the row.

Now we get to the tricky part. If there are no warnings then I just get an empty report. What I want to do is display a message that indicates that there are no warnings.
So, I created a new row and set this expression in the first column:
=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,"No Commitment Warnings", nothing)

And set visibility as:
=IIF(Count(Fields!ALERT_VALUE.Value,"DataSet1")=0,False,True)

But I get nothing.
I think I read somewhere that I an just hiding the rows and not removing them from the report so I guess that my message row is still seeing the hidden rows and that is whey it does not work.
But I swear I had it working this morning LOL!
So, is there a way to get what I am trying to do? Hide all the no warning rows and if there are no warning rows, show the message?


texts:
I have table with jsonb field - timetable which has next structure:

{
1: [{start:0, finish: 12}],
2: [{start:0, finish: 12}, {start:12, finish:17}],
3: [{start:12, finish: 17}],
4: [{start:0, finish: 12}],
5: [{start:12, finish:17}, {start:17, finish: 0}],
6: [{start:0, finish: 12}, {start:12, finish:17}],
7: [{start:17, finish: 0}]
}

1...7 are days of week, which represent when user is available - morning/afternoon/evening.
I'm doing filter where user could choose morning/afternoon/evening and days of week and get available users. If user checked all three time periods and all days I will have 21 variants. I wrote next query:

SELECT * FROM users WHERE
timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
......
timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}

Please advice me how to optimize my query. I know if user checked all possible periods and days I could just query with timetable is not null. But I want to optimize my task in general.


texts: How to get all months in a query where one month has no matches?

texts:
I need to obtain every month by year (to fill up a graph). However, I found out that the first month had no data whatsoever and I wanna reflect that. The query is this one:

SELECT tblMes.id, COUNT(*) AS ingreso
FROM tblTicket
LEFT JOIN tblMes ON MONTH(tblTicket.fechaIngreso) = tblMes.id
WHERE year(fechaIngreso) = 2017
GROUP BY tblMes.id
ORDER BY tblMes.id asc

The result of the query is this one:

Month | Qty
--------------
"2"   | "1066"
"3"   | "1395"
"4"   | "761"
"5"   | "1316"
"6"   | "879"
"7"   | "1039"
"8"   | "1099"
"9"   | "577"
"10"  | "1064"
"11"  | "1268"
"12"  | "1188"

The first row should be "1" | "0"

Why is the left join not enough?


texts: Multiple Location AlwaysOn and Azure SQL Server

texts:
AS with the majority of projects, the original scope may not be a viable option. We were using Azure Data Sync and transaction replication to keep the 4 site databases in sync. However, the customer will have user-defined datatypes which are not compatible with Azure Data Sync and, they are now wanting to utilize Azure with AlwaysOn AG groups. I’ve setup a couple SQL Server AG’s, however, not to this extent and I’m really hoping someone has some good advice on how to go about this.

Requirements:

Databases must remain accessible my all sites if a site should lose connectivity.
Databases must be synchronized at all times.
Minimal impact on changing connection strings to over 100 applications.
24 x 7 operation.
New database after ETL will contain the schema used by all sites.

Site Setup:

1 Physical SQL Server per site (customer is aware they’ll need additional
SQL Servers).
Number of sites = 4

Goal:

To achieve a solution in which all sites are able to remain functional should a connection, server etc. go down. In addition to utilizing Azure in the process. Each site should also maintain an updated version of the database.


texts: Eliminating duplicate records in data cleansing

texts: I have a database full of records of people with simple information like first name, last name, email, location, ... . I need to eliminate the duplicate records. As I've search the process is called "duplicate elimination in Data Cleansing". Does anyone know a good open source tool to do that?


texts: Load data - fields terminated by character appearing within a field statement

texts:
I have an issue with loading data from a csv file. The question is, without preprocessing, is it possible to have an exception in the following code

load data local infile 'Program.csv' into table program_table
    fields terminated by ','
    LINES TERMINATED BY '\r\n'
    ignore 1 lines
    (@program_code, @field_of_study, @area_of_study, @degree_level)
    SET
        program_code = nullif(@program_code,''),
        field_of_study = nullif(@field_of_study,''),
        area_of_study = nullif(@area_of_study,''),
        degree_level = nullif(@degree_level,'');

that ignores the "terminate by" character when it's followed by a (space). The problem I've encountered is within the csv file there are lines like the following

ZLD6,Administration/Business,BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING,Doctorate.

The BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING should be one field, but the field termination character ','  separates int into two. That is the fields would be ZLD6, Administration/Business, BUSINESS MGMT. SMALL BUSINESS, CONVEYANCING, and Doctorate (5 fields instead of 4).

Any suggestions on possible solutions would be great.


texts: Grid Control Standby database status: Creation status unknown

texts:
After using Grid Control to add standby database, Data Guard status of standby database is showing "Creation status unknown". However, dataguard status on both primary and standby databases are OK. Database is Oracle 11.2.0.3. The Management agent version is 12.1.0.1.
I read on the forum that I need to apply patch #9244383. However, when I apply patch, It requires agent version 10.2.0.5.

What should I do here?


texts: How can I get SQL Server Management Studio 2012 to connect to multiple servers automatically on open?

texts:
When I open SQL Server Management Studio, it only connects to a single Database Server, but I would like it to automatically connect to multiple Database Servers.

How can I accomplish this?


texts: Can I run UPDATEs on a replicated MySQL slave, safely?

texts:
We would like access to accurate MySQL backups for debugging, without having personal information (PII) in the backups.

Is it possible to run UPDATEs on the replicated database (ie. replacing text columns with Lorem Ipsum) without breaking replication?

(Note: The schema allows us to identify newly updated rows very easily - meaning it is trivial for us to identify new PII quickly)


texts: how do I limit the characters in selected row and con-cat with '...'

texts:
I have a query which return a column named comment. I want to limit the number of characters returned by select statement to 50 and if the comment row contain more than 50 characters, I want to append ... at the end of it. How can i achieve this using on SQL query ?

Here is the query for my question.

select comment from mip
where active = 1


texts: Connection Error while connecting SQL Developer with DB2

texts:
I am trying to connect Oracle SQL Developer with DB2 Database.

I have added db2jcc4.jar, db2jcc.jar and db2jcc_license_cu.jar Libraries in "Third Party JDBC Driver"
DB2 tabs appears
Entered Username and connection Details.

While testing connection, I encounterd below error.

  Status : Failure -Test failed: [jcc][t4][2034][11148][4.24.92] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
  A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

Versions:

db2level: v11.1

./java com.ibm.db2.jcc.DB2Jcc -version

IBM DB2 JDBC Universal Driver Architecture 3.72.44

Jars installed:

echo $CLASSPATH

/db2/coru1db2/home/coru1db2/sqllib/java/db2java.zip:
/db2/coru1db2/home/coru1db2/sqllib/java/sqlj.zip:
/db2/coru1db2/home/coru1db2/sqllib/function:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc_license_cu.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/clpplus.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/jline-0.9.93.jar:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc.jar:

Can someone help me in resolving it?


texts: Azure SQL Database finding latest available restore point

texts:
From reading books online it is clear that there are two types of retention for Azure SQL Database.

Backups for point-in-time restore
Backups for long-term retention

When it comes to restore there are multiple options.

Point-in-time restore
Deleted database restore
Geo-restore

When I think of Geo-Restore the latest restore point cannot be same as Point in-time restore as backup has to be copied in a different region/data center.

  There is a delay between when a differential backup is taken and when
  it is geo-replicated to an Azure blob in a different region. This
  delay can be up to an hour, so, if a disaster occurs, there can be up
  to one hour data loss.

To find the latest restore point for Geo backup I can use Get-AzureRmSqlDatabaseGeoBackup command.

Question:

How can I find out LastAvailableBackupDate for point in-time restore?


texts: Updating table schema with correct datatype in sqlite

texts:
I know that importing from CSV into SQLite treats all fields as text. According to How to import csv file to sqlite with correct data types, one can use

UPDATE MyTable SET MyColumn = CAST(MyColumn AS INTEGER)

to update whole colum type.  That seems to work, however that doesn't seem to be reflected in the output of TYPEOF() nor in PRAGMA table_info(table_name)

sqlite> CREATE TABLE testtb (a TEXT, b TEXT);
sqlite> INSERT INTO testtb VALUES ('1', '2');
sqlite> UPDATE testtb SET b = CAST(b AS INTEGER);
sqlite> SELECT TYPEOF(b) FROM testtb;
text
sqlite> PRAGMA table_info(testtb);
0|a|TEXT|0||0
1|b|TEXT|0||0

So how can I make the changes actually show up  when querying table's meta information (I'm lacking a proper phrasing here, but hopefully this makes sense) ?


texts: Whats better: identity columns or generated unique id values?

texts:
I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created.

I am arguing for the sql managed identity column because there will be no contention created and keep overhead down.

My partner is arguing for generating the values manually via another table where we can have a UDF lock the resource increment an ID table with one field called ID_Value by +1 and use this as a global unique identifier. Or have the table do an id+1 when inserting. The logic here is that it would be simple to move data between servers and/or environments not having the identify constraint.

Without running tests on his solution I doubt it is at all scalable because all inserts are not bottle necked via this id generation, or each table id generation.

Also, I don’t believe moving chunks of records is all that difficult is it, even with the identity constraint?

Who is right and please detail the exact reasoning.

EDIT:
when I say moving databases or servers or environments i mean moving from one DB where there is data to another similar DB with lets say staging or dummy data. For testing in non prod we may want to pull all records from yesterday down to staging for testing. this is what i meant with moving data.

EDIT ~ drachenstern
For those following along, this question was dual-posted, migrated from SO, and merged once it got to this site. Just trying to keep valid conversations on one place.


texts: What is the fastest way to make a copy of a 5TB SQL Server DB on the same server without having to fully reinitialize the DB to add it to the AG

texts:
I have a 3 node Always On AG that hosts a 5TB database. Due to business requirements, at 12:01am on the first day of every month, I need to create a copy of that database on the 3rd node (replica) appended with the previous month name. For example, on April 1st, I create a DB called "mydatabase_march2021" which is a copy of "mydatabase" current as of April 1st at 12:01am.
My current process involves removing the DB from the AG on the 3rd node promptly at 12:01am, renaming it, and then starting the slow process of backing up and restoring the 5TB DB to the 3rd node and adding it to the AG. Is there some other way I can do this without having to fully reinitialize the DB on the 3rd node? I need to have this copy available to business users by 5am.
I have tried removing the DB from the AG, offlining SQL, making a copy of the DB files, but there is no way to attach those files and keep the DB in recovering (which is necessary to reinitialize).
Additional Information:
Windows Server 2016 Standard
SQL Server 2012 SP4 Enterprise (will be upgrading to SQL 2016 or 2019 at some point in the next year)
Node 1 is in datacenter A
Node 2 and Node 3 are in datacenter B


texts: If NoSQL stands for "Not only SQL", is SQL a subset of NoSQL?

texts:
The definition is a bit confusing - basically I'm asking if SQL is a subset of the NoSQl family:

I'm asking this because "Not-only" means NoSQL is much larger, but still includes SQL as a part of it.
On the other hand, since we can't do typical sql operations like joins in a NoSQL database, then SQL is not part of nosql!
I'm wondering which holds true?


texts: Import CSV file into Mysql with Multiple Delimiters/Field Separators

texts:
I'm trying to import a large csv file into Mysql.  Unfortunately, the data within the file is separated both by spaces and tabs.

As a result, whenever I load the data into my table, I end up with countless empty cells (because Mysql only recognizes one field separator). Modifying the data before importing it is not an option, as I'm working with something like 400 million rows.

Here is an example of the data:

# 1574    1 1 1
$ 1587    6 6 2
$115 1878    8 9 23

(Where the second and third value of every row are separated by a tab)

Any ideas?


texts:
With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.

For example, you need a user to create a specific table object in a specific schema. Rather than giving them wholesale create table permission, you could wrap the create table in a stored procedure and give them access to only that. In this way they would be constrained to only creating the object as specified within the procedure and not have any direct create table permission on the database... at least I know it works this way with Select, Insert, Update, Delete. The Create Table statement in the Stored Procedure throws the Permission Denied error.

Am I mistaken or just doing something wrong?


texts: Who has SHOWPLAN permissions on a database

texts: Is there a way I can see all users in a database who currently have access to view execution plans? That is, who has SHOWPLAN permissions


texts: Expand indexes on Structure Tab by default

texts:
I am wondering if it is possible to expand the indexes by default by adding something to the config.inc.php file.

This is how it looks by default:

This is what it looks like when expanded. I want this by default

In earlier versions of phpMyAdmin the indexes-table was visible without expanding it manually. So I think there is an option for that but I couldn't find it in the docs.


texts: Clustered vs Nonclustered indexes, similar to arrays vs linked lists?

texts:
I'm just starting out as a DBA, and have recently been interested in indexes and how they speed up queries.  I was reading about indexes here:
https://msdn.microsoft.com/en-us/library/ms190457.aspx and trying to understand the difference between a clustered index and a non-clustered index in SQL Server.

From what I read, it sounds like a clustered index sorts the data so the index always 'knows' where the next value lies, directly next to it. This reminded me of an array with contiguous memory.  Similarly, a nonclustered index sounded like a linked list, in that there is a part of the index which holds the value, and also a part  of the index which points to the next index.  Is this an apt compiarison to make?


texts: How to Move All tables from one Database into Different Database?

texts: I am using Oracle SQL Developer and I have ten tables. Now I want to move all 10 tables from Oracle into a SQL Server 2008 R2 database. Is there any easy solution for this task?


texts: Comparing binary 0x and 0x00 turns out to be equal on SQL Server

texts:
It seems that SQL Server considers 0x and 0x00 equal values:

SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END

This outputs 1.

How can I get true binary bit-for-bit comparison behavior? Also, what are the exact rules under which two (var)binary values are considered equal?

Also note the following behavior:

--prints just one of the values
SELECT DISTINCT [Data]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

--prints the obvious length values 1, 2 and 3
SELECT DATALENGTH([Data]) AS [DATALENGTH], LEN([Data]) AS [LEN]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

Background of the question is that I'm trying to deduplicate binary data. I need to GROUP BY binary data, not just compare two values. I'm glad I even noticed this problem.

Note, that HASHBYTES does not support LOBs. I'd also like to find a simpler solution.


texts: Pgpool load balancing for a specific database

texts:
we have around 50 databases in QA environment. Please clarify how can I mention in database_redirect_preference_list to enable load balancing only for a single database.
Should I need to mention all databases with load balance ratio 0? Please let me know if pgpool have any other ways to mention it


texts: Design: Fitting variable hierarchy of text files into a relational db

texts:
I have a large data corpus (several TB) made up of data gathered through customer support queries. I've been asked to set up a database to make this data more accessible, but as I'm kind of new to database design, I'm stuck on what may be a fairly trivial design decision.

One of the primary components of this data is a copy of the software configuration for each user's install. The configuration consists of N plain text files, in a fixed directory structure, but each directory can contain a number of different config files depending on how the user has configured their install.

Example hierarchy:

conf
|-- general.cfg
|-- instances
|   |-- exchange_mediator.cfg
|   |-- primary.cfg
|   `-- sip.cfg
|-- settings.cfg
`-- users
    |-- admin
    |   `-- admin_setup.cfg
    |-- admin.cfg
    |-- restricted_user.cfg
    `-- terry.cfg

As mentioned above, different installs may have a different number of files in any given directory, and they may be named differently also.  I'm not sure how to fit the contents of these files (just a simple text blob) into a relational database so that it's easy to search for certain strings in the contents of some or any of these files.

Example queries might be:
+Show me all config files in "instances" containing the string "restricted"
+Show me all support instances with a "user" config file named "admin.cfg"

Any advice would be much appreciated!

Note:
In terms of the specific database I'm using, I'm still researching that. I've looked at NoSQL db's such as elasticsearch and mongodb, as it seems like creating searchable JSON objects would be a great way of handling this.  I'd rather avoid elasticsearch, as their documentation recommends keeping a separate database to store the data reliably (I'd rather avoid that).  I've heard bad things about mongodb in terms of scalability, particularly if you have a large number of indexed keys.

For a more traditional-style SQL db, I'm looking at Amazon RDS or Redshift. Redshift seems to be easy to deploy, and quite capable of handling the amount of data I have, but as it doesn't support datatypes like arrays and structures, I'm not sure how my data could be transformed to fit into a series of tables.

Many thanks for having read this far!

Toby


texts: What happens to an SSIS package when I alter a table that is used as a destination?

texts:
My apologizes if this question doesn't correspond to this site.
I have this question because in a SQL Server is deployed a SSIS Package (is a very old package) and I don't have the dtsx file.

Now I need to add some columns to a table, but this table is used as a destination on this package. So my question is, if I alter the table, will the package continue to work?


texts:
I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is either null or defaulted to "00000000". However, going forward, we're wanting it to be set up that no duplicate values are allowed (lets say 1,000 records are null, 500 are "00000000", and ~10,000 are actual unique values we want).

Looking into it, since I have multiple records which are set to "00000000" and multiple which are null, I understand that I can't use the Unique constraint. Is there a way that I can create a Check constraint that checks if the value being inserted/updated is unique if not all 0's or null? Or if it's not workable via a check constraint, my other thought was maybe use an insert/update trigger to somehow validate the data?


texts: Mongo connection problem in Kubernetes with Rails

texts:
I am deploying mongodb to Kubernetes with following values:

mongodb:
  persistence:
    enabled: true
    existingClaim: mongodb-pvc
  volumePermissions:
    enabled: true
  mongodbRootPassword: hello
  mongodbUsername: db_user
  mongodbPassword: alongpassword
  mongodbDatabase: db_read
  replicaCount: 1
  usePassword: true

The chart gets deployed without any problem.
But problem arises when connecting from rails app deployed in same cluster
I set ENV like

- name: MONGO_DB_URI
  value: {{ template "core.mongodb.uri" . }}

and have this in template

{{- define "core.mongodb.uri" -}}
{{- printf "mongodb://%s:%s@%s-%s:27017" .Values.mongodb.user .Values.mongodb.password .Release.Name "mongodb.default.svc.cluster.local" -}}
{{- end -}}

The values are supplied correctly
But when rails tries to connect the following error is thrown

Mongo::Auth::Unauthorized (User db_user (mechanism: scram) is not authorized to access admin (auth source: admin, used mechanism: SCRAM-SHA-1, used server: relese-name-mongodb.default.svc.cluster.local:27017 (STANDALONE)): Authentication failed. (on crelese-name-mongodb.default.svc.cluster.local:27017))

and get following in mongo log

2020-02-25T18:36:33.722+0000 I ACCESS   [conn2050] Supported SASL mechanisms requested for unknown user 'db_user@admin'
2020-02-25T18:36:33.733+0000 I ACCESS   [conn2050] SASL SCRAM-SHA-1 authentication failed for db_user on admin from client 10.244.0.227:34092 ; UserNotFound: Could not find user db_user@admin


texts:
I have a database with 160 VLFs.  How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation?

Another thing that I want to confirm: Are the following 3 statements always true for newly added chunks in SQL server Virtual Log files?

chunks less than 64MB and up to 64MB = 4 VLFs
chunks larger than 64MB and up to 1GB = 8 VLFs
chunks larger than 1GB = 16 VLFs


texts: Anyone understand why this query no longer works?

texts:
On Localhost this query works correctly, running normal MySQL:

SELECT COUNT(*) as rows, customers. *
FROM customers
WHERE user_id = 6
      AND age = 10
ORDER BY timestamp DESC LIMIT 0

However on the latest mariadb it throws up this:

SELECT COUNT(*) as rows, customers. *
FROM customers
WHERE user_id = 6
      AND age = 10
ORDER BY timestamp DESC LIMIT 0

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows, customers. * FROM customers WHERE user_id = 6 AND age' at line 1
  Time: 0.011s

Anyone know why this would be?


texts: Oracle EXPDP using QUERY parameter with results from SELECT

texts:
Oracle 11.2g XE

I'm trying to write a initial deployment script for my application. There are several tables with data that I want to include in datapump export with a QUERY.

I have a table which includes these queries and table names to be exported.

Table: T_DEFINITION
============================

TABLE_NAME | WHERE_CONDITION
-----------+----------------
TBL1       | WHERE CMP_ID=0
TBL2       | WHERE 1=0
TBL3       | WHERE SEQ_ID=10 OR CMP_ID=0
...

Relevant portions of my expdp PARFILE as follows:

INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM T_DEFINITION WHERE (WHERE_CONDITION!= '1=0' OR WHERE_CONDITION IS NULL))"
QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
...

As you can see, I must have manually define QUERY parameter for each table that I need. Is there a way to define QUERY parameters that SELECT where conditions from my T_DEFINITION table?

Something like this:

QUERY="SELECT TABLE_NAME||':'||WHERE_CONDITION FROM T_DEFINITION"


texts: The service broker is administratively disabled

texts:
I have just restored some databases what use service broker from LIVE to staging.

it is not working and when I put a profile trace to run it only shows messages related to database id 1 and 4, something is wrong but I am struggling to identity what is causing this behaviour.


texts: How to get capacity specifications for a particular Oracle release?

texts:
How to get capacity specifications for new Oracle release(Oracle 11.2.0.4)?
ie, information like

How much schema we can create?,

How much tables a schema can accommodate?

How much rows total instance can have?

How can I get these information? Or from where I can get these information?

Thanks in advance!!


texts: How to represent foreign key in an ER diagram?

texts:
Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that the 'Customer ID' is the foreign key of 'Transactions' Table which is the primary key in the 'Customer' table?

I googled this question and also checked this forum for my query but couldn't find an exact example with a diagram addressing my question.

Please explain me, if possible, with a diagram.


texts:
I'm having some trouble using replication when the machines are configured to use host names instead of IP addresses. The "skip-name-resolve" is OFF.

If I use IP addresses, everything is fine. Using DNS names, it all stops.

On the Master

Replication is configured with the following command:

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'slavename.internal.domain' IDENTIFIED BY 'repl_pwd';

On the Slave

The slave connection is configured as such:

change master to
    master_host='mastername',
    master_user='repluser',
    master_password='repl_pwd',
    master_log_file='mastername.000001',
    master_log_pos=1;

The Error I am Getting

When starting the replication slave, the log file shows the following messages:

120127 14:07:48 [Note] Slave SQL thread initialized, starting replication in log 'mastername.000001' at position 4, relay log './mysql-relay-bin.000001' position: 4
120127 14:07:48 [ERROR] Slave I/O: error connecting to master 'repluser@mastername:3306' - retry-time: 60  retries: 86400, Error_code: 1130

If I then try to connect using the command line from the client machine using:

mysql -h mastername -u repluser -prepl_pwd

I get the error:

ERROR 1130 (HY000): Host 'the.ip.address.here' is not allowed to connect to this MySQL server

I really hope someone out there can help me with my domain resolution issues!!!


texts: Transaction Log file of the secondary database in Log Shipping grows fast and fills up disk space

texts:
I have a log shipping setup for DR. The transaction log file of one of the secondary databases, which is in restoring mode, is 7 times larger than the data file and is filling up the disk space. What are the possible causes and fixes?

Note that it is the transaction log file of the secondary database in the DR site that is growing fast.

For your information, for the corresponding primary database, I take a Full backup every night and transaction log backup every 30 minutes. The transaction logs are shipped every 1 hour to the secondary database.

Thanks


texts: How to design efficient queries and structure for data logging table (numerical stats)

texts:
I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience database people if the basic approach here is sane and what problems and improvements to look out for.

The simple idea is to hash the value ID in application and use it as the key to Stats table. Then this ID is also inserted to StatIds table. As the keys aren't database generated, it looks to me this design also scales to independent databases and the data can still be joined later should such a thing needed.

Data is always logged in UTC time.

Apart from the above mentioned, design criteria:

Should support analytic range queries using SomeId and StatId between some dates.
Should support analytic range queries across the whole table using SomeId and StatId.
Be able to count a specific number of events or occurrence of some events over a specific threshold in some time range.

A more specific example would be to create, say, a rolling average on memory consumption (StatId) on some computer (SomeId) during some month. Or give rolling average on memory consumption of all computers during some month.

Questions:

Is the following basic design sound?
How about the index? The index grows all and probably something should be done to maintain it. Suggestions or advice?
Is there a useful design, about equivalent to this using column store and/or in-memory tables. If so, I'd like very much to see a concrete piece of syntax or reasoning how to go about that. :)
What could be done to anticipate the inevitable data purging (especially if using the more exotic column store and in-memory options)? I read Painless management of a logging table in SQL Server that points out on using table partitioning and moving old data to different tables, but that's not always an option. I wonder if there are other functioning options, such as creating and automated job that moves data (but what about index fragmentation then etc.).
Due to range queries, clustered index look like the way to go for me here. Is that correct?
Is the order of columns an effective one assuming the queries or should it perhaps be (StatId, CreatedOn, SomeId) or something else?

I don't think this needs to be super-scaleable due to inserts, but supporting analytics queries such as Give max/min/average memory consumption (see previous) is what's wanted.

-- Here StatId is application specific hash made of StatName.
CREATE TABLE StatsIds
(
    StatId      INT NOT NULL PRIMARY KEY,
    StatName    NVARCHAR(150)
);

-- Here SomeId is application specific hash made of SomeName.
CREATE TABLE SomeIds
(
    SomeId      INT NOT NULL PRIMARY KEY,
    SomeName    NVARCHAR(150)
);

CREATE TABLE Stats
(
    StatId      INT             NOT NULL,
    SomeId      INT             NOT NULL,
    Value       REAL            NOT NULL,
    CreatedOn   DATETIME2(3)    NOT NULL

   -- Perhaps this should be a covering index over Value?
   CONSTRAINT PK_Stats PRIMARY KEY CLUSTERED(CreatedOn, StatId, SomeId)
);

I included both MySQL and PostgreSQL too since it doesn't hurt to see what could translate into other storage engines too. PostgreSQL, for instance, looks like something that may have plugins or efficient ways to handle a situation like this.

<edit: An interesting link on PostgreSQL and time-series and in general.


texts: How to write this SQL query for a XML type?

texts:
I am using SQL Server 2012 in which I have a table [abc] which  has  a column called [Information] of the [XML] type.

This column has values like -

<Information>
  <Groups>
    <Group Name="Monitor">
      <Items>
        <Item>
          <Name>Cid</Name>
          <Value>2323232323</Value>
        </Item>
        <Item>
          <Name>Tid</Name>
          <Value>6656565656</Value>
        </Item>
      </Items>
    </Group>
  </Groups>
</Information>

How can I write a SQL Query which can query this column and further extract the value [Tid] ?


texts: Is it possible for total participation constraints to be enforced by an attribute?

texts:
For a school Food Delivery application I am developing, I've been told to use the most accurate ER model that captures as many constraints as possible. In particular, I have decided to enforce a given constraint

"Each order’s food items must be from a single restaurant"

using an aggregation from Orders to Restaurant-FoodItem relationship. However, as the application layer requires to get a restaurant_id associated to an Order, what I am currently doing to get restaurant_id is to join all the way from Orders to Restaurants, following the edges on the ER model.

There was another alternative suggested by my teammate to just put restaurant_id as an attribute in Orders to enforce the constraint as well as allow easier more efficient retrieval of restaurant_id. May I know whether the suggested alternative allows us to enforce the earlier constraint?


texts: Design critique on a table to keep track of which person flipped a switch at what time

texts:
I created a table to keep track of which person flipped a switch at what time. It consists of:

create table records
( name varchar not null
, changed_at timestamp with time zone not null
, organization varchar not null
, value bool not null)

To determine the latest value, I can do:

select value, name
from records
where organization=?
order by changed_at desc
limit 1;

I need to keep track of all changes to the system, hence this schema.

However, I’m considering making a separate table for organizations, and then use a foreign key constraint on records.organization.

Please critique my table choice.


texts: ORA-00942 when trying to select from gv$session inside of procedure

texts:
I have been tasked with allowing developers to kill their own sessions in the Development database. I've written a procedure, but for some reason I'm getting PL/SQL: ORA-00942: table or view does not exist when trying to call gv$session. Here's the proc, being compiled as SYSTEM. Running on an 11.2.0.4.3, 2 node RAC database:

create or replace procedure kill_dev_session (v_osname in varchar2)
is
  lv_env     VARCHAR2(30 char);
  li_inst_id number;
  li_sid     number;
  li_serial  number;
begin
  select replace(global_name, '.DOMAIN.COM', '')
    into lv_env
    from global_name;

  select inst_id, sid, serial#
    into li_inst_id, li_sid, li_serial
    from sys.gv$session
   where upper(osuser) = upper(v_osname)
  group by inst_id, sid, serial#;

  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION'||''||li_sid||','||li_serial||','||li_inst_id||'''';

  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => lv_env||'@db01.domain.com',
              recipients => 'dba@domain.com',
              subject => 'Session killed by '||v_osname||' user in '||lv_env,
              message => 'This user killed a session using the kill_dev_session procedure.',
            mime_type => 'text; charset=us-ascii');

end;
/

So here's the rub: every single developer logs in with the same user, so I can't really use username. I don't want to open up blanket alter system for every single user. This would allow a dev manager to pass in a username (i.e. jsmith) and kill that session, and would email us DBAs that this happened, so we can see if the devs are doing this a lot.

Any thoughts as to why I'm seeing this:
    Errors for PROCEDURE KILL_DEV_SESSION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/3     PL/SQL: SQL Statement ignored
14/10    PL/SQL: ORA-00942: table or view does not exist

Thanks in advance!


texts: Facing permission error while trying to execute a stored procedure published through CLR

texts:
I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to execute the procedure.
  The error is:

  Msg 6522, Level 16, State 1, Procedure cust_diff, Line 0 [Batch Start
  Line 2] A .NET Framework error occurred during execution of
  user-defined routine or   aggregate "cust_diff":
  System.Security.SecurityException:
  System.Security.Permissions.SecurityPermission
  System.Security.SecurityException:  at LevenshteinDistance.cust_diff()

I've added these two at the beginning of my code in C#

using System.Security;
using System.Security.Permissions;

and also this is part of my code that I use to create the procedure:

 [Microsoft.SqlServer.Server.SqlProcedure]
 [PermissionSet(SecurityAction.Assert, Unrestricted = true)]
 public static void cust_diff()
 {

    // Put your code here
    LevenshteinDistance ld = new LevenshteinDistance();
    ld.Method1();
 }

but despite using these two, I cannot execute my procedure in SSMS.


texts:
I have hit a rather frustrating snag in that after installing Oracle 18c.

I have been unable to successfully create a user and then connect to it.

More precisely I can create the user but the connection keeps failing. The commands I’m using are below along with the failed connection attempt. If you have any suggestions it would be greatly appreciated.

I first connect to Oracle as root and then connect to the container XEPDB1

sqlplus system/password

SQL>ALTER SESSION SET CONTAINER = XEPDB1;

I then do the following to create a user.

SQL>create user paul identified by Guardium111;

SQL>grant connect to paul;

SQL>grant create session to paul;

SQL>alter user paul default role all;

Everything above works without error. When I try to connect to user paul I get the following. I’m tearing what little is left of my hair out trying to understand what the issue is.

SQL> conn paul

Enter password:

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.


texts: Oracle error ORA-65052: statement involves operations with different container scope

texts:
I'm a relative newb to Oracle. I have set up an instance of Oracle 12c running on a RHEL 7 VM. I am connecting to the database created during installation through and running some installation scripts.

Below is the output from the script that I am trying to run, can anyone point me in the right direction? I have a feeling I'm doing something fundamentally wrong.

Error starting at line : 4 in command -
GRANT CONNECT TO TDSUSER IDENTIFIED BY "TDSPASSWORD"
Error report -
SQL Error: ORA-65052: statement involves operations with different container scope
65052. 00000 -  "statement involves operations with different container scope"
*Cause:    An attempt was made to combine one operation that applies to all
           containers with another that only applies to the local container
           into one statement.
*Action:   Execute the operations in separate statements.
old:ALTER USER TDSUSER DEFAULT TABLESPACE &TBLSPC_TDS_TABLES TEMPORARY TABLESPACE &TBLSPC_TDS_TEMP PROFILE &DBPROFILE
new:ALTER USER TDSUSER DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE TRIDION_SYS

Error starting at line : 6 in command -
ALTER USER TDSUSER DEFAULT TABLESPACE &TBLSPC_TDS_TABLES TEMPORARY TABLESPACE &TBLSPC_TDS_TEMP PROFILE &DBPROFILE
Error report -
SQL Error: ORA-02380: profile TRIDION_SYS does not exist
02380. 00000 -  "profile %s does not exist"
*Cause:    Try to assign a user to a non-existant profile
*Action:


texts: Restore specific date from mysql dump file

texts:
i have a big mysql dump-file since 2015 i want to restore all data form 2020 and 2021
Note : schema have a lot of tables and every table have it's own data
and we didn't want all table we need only 3 table with specific date
for example i have table called "datamart_queue_details" and date into column called "Event_time" i want to extract all data from table "datamart_queue_details" where  "event_time" > 2020-01-01


texts: Add new columns on existing or auxiliary table?

texts:
I have a database with the following table:

stores
- ID
- name
- phone number
- email address

After adding around 4000 stores to the database we figured that we will need to allow store owners to create a more complete profile. This means we will have the following optional fields:

address, zip code, description  and website URL

My question: should I add these new columns on the existing table or create a new one called store_details, with a foreign key to main table?

I am using MySQL, and each store can only have 1 of those fields.

Consider that only 30% of store owners are going to use them by creating a complete profile.

I am mostly worried with performance when the database grows to say 500,000 entries. For example, when fetching all stores to perform a keyword search. One could argue that by using an axillary table I could save some memory both on disk and on RAM when manipulating the data, but not not sure if this is significant or not.


texts: Why is the schema name necessary here and how to compile without it

texts:
I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form.
Here's a snippet to illustrate the problem (I changed the identifiers name but it doesn't matter):
PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 relation.c_arg1%type;
  v_arg2 relation.c_arg2%type;
  v_arg3 relation.c_arg3%type;
BEGIN

  SELECT r.c_arg1, r.c_arg2, r.c_arg3
    INTO v_arg1, v_arg2, v_arg3
    FROM relation r
   WHERE ...

  -- Some more code ...

END;

When I'm compiling, the error I get is :
Compilation errors on SOME_PROCEDURE:
PL/SQL ERROR 302 at line 2, column 19
component 'C_ARG1' must be declared
PL/SQL ERROR 0 at line 2, column 10
Item ignored
PL/SQL ERROR 302 at line 3, column 19
component 'C_ARG2' must be declared
PL/SQL ERROR 0 at line 3, column 10
Item ignored
PL/SQL ERROR 302 at line 4, column 19
component 'C_ARG3' must be declared
PL/SQL ERROR 0 at line 4, column 10
Item ignored

relation here is a table name and is causing the problem. If, for testing purposes, I pick another table and field name, the code compiles fine e.g. v_arg1 other_table.other_field%type;.
I figured maybe relation is a reserved keyword (even though I couldn't find anything in the documentation about this specific keyword, yikes), so I tried to put the schema name admin before the table name:
PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 admin.relation.c_arg1%type;
  v_arg2 admin.relation.c_arg2%type;
  v_arg3 admin.relation.c_arg3%type;
BEGIN

And it works just fine. Now this is a problem, because admin is not a fixed name and will change because multiple schemas are used for multiple purposes ie development, testing, production, etc.
This is where I'm stuck. I don't know how to do this the "right" way: because the %type is a VARCHAR2 with some length, I'll use something larger than usual such as VARCHAR2(100) and hopefully it downcasts without errors in the future.
My question is, is it possible to come up with a way to reference this table without specifying the schema?
This is for my work, I don't have much freedom in changing the table/schema name, etc.
Thank you


texts:
I'm trying to figure out how to split a single query into two separate queries. The following two queries is what I want:

Query #1:

Label            Value
TOTALSTUFF       100

Query #2:

Label           Value
NONSTUFF        50

Currently the query displays like this:

TOTALSTUFF     NONSTUFF
100            50

If someone would be so kind as to help me out regarding the pattern(s) I should look for when reading/reconstructing these types of queries it would be awesome, since there will be more I'd need to do this with. That may or may not be possible but I'm giving everything a shot at this point.

Here's the original query:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0')
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders -
countpositivecontact),'##,##0') AS 'NONSTUFF'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';


texts: Require assistance in writing a mysql query for the problem below

texts:
I need help writing a query to display hotel id, hotel name and hotel type of
hotels which have not taken any orders in the month of 'MAY 19'. Sort the result based on hotel id in ascending order.

Assume we have two table hotel_details and orders.

I have tried the following code

select hotel_id,hotel_name,hotel_type from hotel_details
natural join orders
where order_id is null and monthname(order_date)="May"
group by(hotel_id);


texts: Why is this dockerized Postgres not able to authenticate with a password?

texts:
I am working on changes to an Alpine fork of the official Docker postgres image ( https://hub.docker.com/r/deinspanjer/docker-postgres95-alpine34/ ).  It has been working fine for a while, but I needed to do some fdw connections, and that required host authentication to be set up so the user mapping could have a password.

You can see the startup / initdb code in the docker-entrypoint.sh file, but the meat of the matter is this:

Takes advantage of the POSTGRES_USER and POSTGRES_PASSWORD environment variables
Before calling initdb, writes the plain password to /tmp/.pwfile and writes the line: *:*:*:$POSTGRES_USER:$POSTGRES_PASSWORD to ~/.pgpass (chmod 600).
Calls initdb passing the --pwfile=/tmp/.pwfile argument to try to get it to initialize the superuser with the desired password.
Alters the pg_hba.conf file to add an additional line for host all all 0.0.0.0/0 md5

The problem is, after initdb, it starts up the server listening only on localhost to continue with the rest of the bootstrapping, but when it tries to run psql --username $POSTGRES_USER, it gets an authentication failure.

The log indicates that it is using the .pgpass, so I'm wondering if the initdb isn't picking up the password right or something, but I've fought with it until late in the night tonight, and I haven't been able to crack it.

If anyone else might be able to help, I would greatly appreciate it.

If you want to try running the entrypoint script manually, here is how you can do it:

docker run -e POSTGRES_USER=testuser -e POSTGRES_PASSWORD=testpass -e POSTGRES_INITDB_ARGS="-A md5" -p 5432:5432 -it --rm --entrypoint bash deinspanjer/docker-postgres95-alpine34
# printenv | grep POSTGRES
# /docker-entrypoint.sh postgres


texts: Install mysql-workbench failed: Requires: proj, tinyxml, python-paramiko >= 1.15.1

texts:
I am installing mysql-workbench to migrate Postgres database to Mysql.

  OS System Version: Red Hat Enterprise Linux Server release 7.2 (Maipo)

I am installing mysql-workbench and follow these instructions.

https://dev.mysql.com/doc/workbench/en/wb-installing-linux.html

I have the following repo.

repo id                                                                                repo name                                                                                status
cloudera-manager                                                                         Cloudera Manager, Version 5.7.5                                                                              7
mysql-connectors-community/x86_64                                                        MySQL Connectors Community                                                                                30
mysql-tools-community/x86_64                                                             MySQL Tools Community                                                                                40
mysql57-community/x86_64                                                                 MySQL 5.7 Community Server                                                                                164
rhui-REGION-client-config-server-7/x86_64                                                Red Hat Update Infrastructure 2.0 Client Configuration Server 7                                              6
rhui-REGION-rhel-server-releases/7Server/x86_64                                          Red Hat Enterprise Linux Server 7 (RPMs)                                                                13,573
rhui-REGION-rhel-server-rh-common/7Server/x86_64                                         Red Hat Enterprise Linux Server 7 RH Common (RPMs)                                                         209
repolist: 14,029

When I tried the command

sudo yum -y install mysql-workbench-community

I got the these erros.

Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: proj
Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: tinyxml
Error: Package: mysql-workbench-community-6.3.8-1.el7.x86_64 (mysql-tools-community)
           Requires: python-paramiko >= 1.15.1

My question is how to find the repo of these dependencies package - proj, tinyxml, python-paramiko?


texts: How to grant execute permissions to a stored procedure but not to the underlying databases

texts:
I am trying to allow a user to run a stored procedure while restricting them read/write access to the underlying tables. I am able to complete this with other stored procedures, but the one I am working with contains tables in a second database.

I have restricted the user from accessing the underlying tables in the database the stored procedure was created in (database ORMAN), but the only way this stored procedure will run is if I give the user READ permissions to the entire second database that contains one of the tables called in the stored procedure (database PSMAN).

This is the setup:

Databases:

ORMAN
PSMAN

User:

BI_User
ORMAN database access, has access to execute stored procedure (BI_Datadownload).
PSMAN database access, assigned db_datareader access.

Stored Procedure (BI_Datadownload):

Found in database ORMAN.
Calls table in another database PSMAN

Any help would be appreciated.

Cheers.


texts: Standard Edition High Availability without Shared Storage

texts:
I'm a software dev with little experience managing SQL Server so please bear with me.

We are developing a piece of software that needs to be highly available, with a significant degree of resilience. This application will use SQL Server to store much of its operational data.

This application will be running from 2 separate physical locations and no shared disk (SANs or whatnot) is allowed between these 2 locations, shared SMB is a possibility but not well liked by our security people.

This application won't commit loads of data to SQL Server, but there will be 26 instances of this system deployed in the real world, 52 SQL Server instances altogether so to cut costs we are pretty sure SQL standard is as expensive as we can afford.

What are my options, since Database Mirroring seems to be being obsoleted in the not too distant future?

EDIT We have the possibility for a SAMBA shared to be replicated between our sites using DFS-R, does this open up any windows? With SQL 2014 STD, you can set up AlwaysOn for 2 nodes is this a viable approach?


texts: Which one to use? InnoDB Replication or MySQL Cluster?

texts:
I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual Machine. I want the application to be easily scalable, I want to be able to add new nodes and make the system perform better as easily as possible when needed.

I have been reading about MySQL Cluster and MySQL Replication, but I haven't been able to decide on which one to use. You can imagine that (a popular) url shortener will be both write and read intensive. What is the structure that you would use in such a case? Would you go for cluster or replication?

Then based on the choice of cluster or replication, what is the infrastructure/configuration that I am supposed to have in order to be able to expand from a single innoDB engined Database to a cluster or replication structure? I want to start correctly, I don't want to be stucked in a situation where I can not expand/improve the database system when I need to.

Thanks A lot

sr.


texts: Failed to allocate BUFs during DBCC CHECKDB

texts:
I am writing to see if anyone can help me out with a problem that I just can't seem to figure out. This one is going to be a doosey and I am going to try and list out as much of the important info that I can but if I miss anything please let me know and I would be happy to provide whatever info you might need to be able to assist if you're willing?
The symptom that I am experiencing is that I have dbcc checkdb locking up when running against a VLDB (approx 1TB) as one of the tasks in a Maintenance Plan and the error log is reporting the error: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 7 (sometimes 8) and then  the error log starts filling up with memory charts (best way I can describe it, see attached screenshot) about physical and virtual memory.
So here is the scenario. We are currently beginning testing of our NEWSERVER before we migrate off our OLDSERVER. Everything is working as expected on our OLDSERVER. The problem is occurring on our NEWSERVER in the PROD instance during our nightly Maintenance Plan routine. There are multiple db's present in the instance but the one that we are concerned with is DB1. DB1 is made up of 2 data files and 1 log file. On the OLDSERVER the .mdf (519 GB) is located on H:, the .ndf (200 GB) is located on E:, and the .ldf (313 GB) on D:. On the NEWSERVER both data files are on E: and the log file is on D:. Note: I was not involved in the configuration of the database having 2 data files or their location, or the setup/configuration of either of the servers.
On the OLDSERVER the maintenance plan (consisting of a Check Database Integrity task, Full Database Backup, and a Maintenance Cleanup Task and is configured to run against DB1 only) completes nightly with no issues. On the NEWSERVER the maintenance plan (set up exactly the same way) will sometimes complete but is mostly slowing to a snail's crawl (or find something even slower than a snail) and will eventually fail during the Check Database Integrity task .
I can run the DBCC CHECKDB manually and sometimes it will complete in a very timely fashion but other times even the manual run will exhibit the same behavior. Not that I know if either of these settings are directly applicable, but I have tried with Lock Pages in Memory both turned on and off, no difference, and I have tried with Instant File Initialization turned both on and off, no difference.
Here are the details of the PHYSICAL server that we'll call NEWSERVER.

OS: Windows Server 2016 Standard - 6.3 (14393)
Processor Count: 32
Memory: 384 GB (382 GB usable)
Drives (All SSD configured with) : OS (C:) -181 GB free of 243 GB | Logs (D:) - 117 GB free of 488 GB | Data (E:) - 2.86 TB free of
3.81 TB | Backup (F:) - 1.44 TB free of 1.9 TB
SQL Server 2016 Standard - SP2 CU3 (13.0.5216.0)
Number of instances: 4 (PROD, DEV, TEST, TRAIN)
Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB)

Here are the details of the PHYSICAL server that we'll call OLDSERVER

OS: Windows Server 2016 Standard - 6.3 (14393)
Processor Count: 24
Memory: 384 GB
Drives (Spindle & SSD Mix): OS (C: SSD) -163 GB free of 249 GB | Logs (D: 15k Spindle) - 197 GB free of 557 GB | ProdData (E: SSD) - 604 GB free of 865 GB | Backup (F: 10k Spindle) - 1.46 TB free of 2.18 TB | NonProdData (G: SSD) - 591 GB free of 1.08 TB | ProdData2 (H: SSD) - 231 GB free of 743 GB
SQL Server 2016 Standard - SP2 CU2 (13.0.5153.0)
Number of instances: 5 (PROD, DEV, TEST, TRAIN, PROD2)
Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB) | PROD2 (32768 MB)

Attached are the first (almost 50) lines of the error log (I can provide more if needed.

Any help or ideas are much appreciated!


texts: How to trace an SSIS package using Extended Events?

texts:
I'm trying to figure out how to trace only SSIS packages accessing a database. I think one way (albeit probably not perfect) to find these queries using SQL are using sys.dm_exec_sessions and checking client_interface_name for the value "OLE DB" (at least for this database). However, I can't find the correct predicate for this in EE. Is there another way?

SQL Server 2012 SP2


texts: Does Postgresql support different transaction isolation per connection?

texts:
I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other connections.

Does Postgresql support the use of different isolation levels for each connection?


texts:
MariaDB is frequently (multiple times a day) restarting.

This is what I get in the mariadb.log

200411 18:38:51 mysqld_safe Number of processes running now: 0
200411 18:38:51 mysqld_safe mysqld restarted
200411 18:38:53 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.HWm8QZ' --pid-file='/var/lib/mysql/db-recover.pid'
200411 18:38:54 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 16103 ...
200411 18:39:01 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
200411 18:39:01 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 16162 ...
200411 18:39:01 [Note] InnoDB: Using mutexes to ref count buffer pool pages
200411 18:39:01 [Note] InnoDB: The InnoDB memory heap is disabled
200411 18:39:01 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
200411 18:39:01 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
200411 18:39:01 [Note] InnoDB: Compressed tables use zlib 1.2.7
200411 18:39:01 [Note] InnoDB: Using Linux native AIO
200411 18:39:01 [Note] InnoDB: Using CPU crc32 instructions
200411 18:39:01 [Note] InnoDB: Initializing buffer pool, size = 4.4G
200411 18:39:02 [Note] InnoDB: Completed initialization of buffer pool
200411 18:39:02 [Note] InnoDB: Highest supported file format is Barracuda.
200411 18:39:02 [Note] InnoDB: 128 rollback segment(s) are active.
200411 18:39:02 [Note] InnoDB: Waiting for purge to start
200411 18:39:02 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.2 started; log sequence number 523546041827
200411 18:39:02 [Note] Plugin 'FEEDBACK' is disabled.
200411 18:39:02 [Note] Server socket created on IP: '::'.
200411 18:39:02 [Warning] 'proxies_priv' entry '@% root@site-mariadb' ignored in --skip-name-resolve mode.
200411 18:39:02 [Note] WSREP: Read nil XID from storage engines, skipping position init
200411 18:39:02 [Note] WSREP: wsrep_load(): loading provider library 'none'
200411 18:39:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.33-MariaDB-wsrep'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server, wsrep_25.21.rc3fc46e

Any ideas what could be going on?


texts: MySQL: Reference aggregate function in conditional select

texts:
I'd like to reference aggregate function like this:

Sample Data:

+-----------+----------+-------+
| member_id | group_id | score |
+-----------+----------+-------+
|         1 |        1 |    12 |
|         2 |        1 |     2 |
|         3 |        1 |    20 |
|         4 |        2 |    40 |
|         5 |        2 |     4 |
|         6 |        3 |    19 |
|         7 |        3 |    17 |
+-----------+----------+-------+

Query:

SELECT MAX(IF(score = MAX(score), member_id, NULL)) AS member
FROM members
GROUP BY group_id

Expected Result:

+-----------+
| member_id |
+-----------+
|         3 |
|         4 |
|         6 |
+-----------+

Fiddle: http://sqlfiddle.com/#!9/08af68/2

But this results in the error:

  Invalid use of group function

I've simplified things here but I'm trying to use this in a much more complex scenario where the score value is the result of a large complex query.  Doing this the standard way which I'm aware of results in something that looks a bit ridiculous since the initial query to get the working values is upwards of 35 lines of code.

QUESTION: Why are aggregate functions not allowed to be used like this?  Is there a workaround that doesn't involve effectively duplicating the entire SELECT simply to get the MAX() value for use as a point of reference?


texts: Disk page reads for Index Nested Loop Join

texts:
I apologise if this is the wrong place to post this question.

In my Advanced Databases course I'm taking, we were taught that the number of disk page reads done when doing an index nested loop join when you have enough buffer space for 3 pages is:

Disk page reads for R INLJ S = |Pages(R)| + |R|.Depth(Index on S) + |R Join S|

I understand that we need to read in all the pages of R as the outer loop and then we have to traverse the depth of the B+ tree to get the leaf pages (and do this |R| times) but why do we need to add the number of times R joins with S?


texts: How to Substitute Variables in PostgreSQL

texts:
The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL?

INSERT INTO control_threshold (
    threshold_id, group_name, description, sql, low_value, high_value)
VALUES(
    threshold_seq.nextval, '&2', '&1',  TRIM('&5' || '&6' || '&7' || '&8' || '&9'),
    trim('&3'), trim('&4'));


texts:
SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2.  Any ideas?
Sql 2008 R2 will not start with trace -f -T3608 or single user mode -m. Need params to start SMS to move Model back.


texts: Is it possible that a same INSERT operation performance will suddenly degrade?

texts:
I am working on a code performance test project.

Let me first describe the enviorment: MsSql Server, Ria Service

The automation test runs a big set of Test cases in midnight where have minimum network traffic or CPU usage on the server.

The tests will calls the Ria Service and the Ria Service will make calls to the database to do same operation repeatedly for couples times.

The performance metric is measured using Sql Profiling Trace, so we got all sql calls that made within each test and we can associates each of them.

I notice sometime a same SQL calls in a test its performance will suddenly jump up.

i.e. An INSERT operation of a test, usually is around 0.807 ms and its jumps to 200ms.

I wonder is this a normal behavior?  Say there is no environment impacts to the test...


texts: What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

texts:
Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "IsDeleted" NOT NULL BIT field with a default of '0'. When the application "deletes" data, it simply updates the IsDeleted flag to 1.

What I'm having trouble understanding is how the indexes on each of the tables should be structured. Right now, every query/join/etc always implements the IsDeleted check. It's a standard that our developers must follow. That being said, I'm trying to determine if all of my clustered primary key indexes on each of the tables need to be altered to include the primary key AND the IsDeleted BIT field. Also, since EVERY query/join/etc. must implement the IsDeleted check, is it an appropriate assumption that EVERY SINGLE index (non-clustered as well) should include the IsDeleted field as the first field of the index?

One other question I have is around filtered indexes. I understand that I could put filters on the indexes such as "WHERE IsDeleted = 0" to reduce the size of the indexes. However, since every join/query will have to implement the IsDeleted check, would that prevent the filtered index from being used (since the IsDeleted column is used in join/query)?

Remember, I do not have the ability to change the IsDeleted approach.


texts: Nullable columns or jsonb for storing user profile?

texts:
I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email and phone. I anticipate that there may be other columns added later, such as mobile and website. Additionally, there may be other non-related fields, such as settings/preferences, saved searches, etc.

I've already decided that I don't want to use any form of key-value store (or any schema involving many-to-many relations) for this, unless there's an extremely good reason for it.

Pros of jsonb:

Can store multiple values for each "column" if necessary
Adding new field just needs JS coding and documentation

Cons of jsonb:

Overhead of storing the "column" name as a string for each "row"
Wonky to perform comparison queries (I think N/A to my usage scenario)
Have to expect the unexpected

Anything else to add to this list of pros/cons? As much as I'd like to simply use nullable columns, I think it would be negligent to ignore jsonb - it seems like a compelling choice.


texts: Cannot install DB2 Express C 10.1 on Mac OS X 10.7.5

texts:
When I invoke ./db2setup it cannot find the JRE where it expects it.  Binary for Java not found as follows:

/private/tmp/db2/expc/db2/macos/install/db2setup: line 606: /tmp/db2.tmp.740/db2/macos/install/../java/jre/bin/java: No such file or directory

I successfully installed DB2 Express C 9.7 last summer before when I was still using the Apple JDK.  I am now getting the JDK from Oracle as it is the only source I know of.

How do I tell the DB2 install where to find Java?


texts: Testing efficiency of a BRIN index in Postgres

texts:
I have found many use cases in our OLAP queries for BRIN indexes, as they are much much smaller and have comparable speed in the same order of magnitude as BTREE indexes.

Typically I use BRIN indexes for monotonically (or close to that) increasing timestamp columns. In our OLTP databases they are closely placed on the disk since naturally they get sequentially written as time goes.

In our warehousing Postgres instance some parts of the tables are loaded in big batches not necessarily ordered by time.

Is there some analytical query which would tell me if the data is placed too randomly to use BRINs even if seemingly the column value distribution should follow all the prerequisites for creating a BRIN index?


texts:
SQL Server has an option for statistics to autoupdate asynchronously (AUTO_UPDATE_STATISTICS_ASYNC).

But, even if this option is ON, from time to time I see locks like this on pic below: say, spid 549 locks spid 42 and spid 42 locks 8 other sessions. As I can see, system spid tries to change old statistics to a newly computed one, so it waits for SCH-M lock to acquire.

Suddenly, it turns out, async stats update is not so async:  any long-running SELECT (which, of course, holds SCH-S lock) can freeze system spid, which, in turn, can gather queue of many other sessions. Even worse, whole queue wait time depends on of locker’s SELECT duration. It means, if I have a long OLAP query, all the stalled sessions could be timed out.

So, my question is: am I missing something or it works just by design? Can I somehow avoid these sudden locks, caused by autoupdated statistics?


texts:
I'm running a large-ish SQL Server Enterprise 2016 Data Warehouse where we've for some time now had issues where we are seeing the following symptoms:

High Disk Queue length, including 100% activity on those disks. The queues are going over 10,000 at times with very low actual throughput, under 100Mb/s compared to over 1.5Gb/s the drives are capable of.
High Disk Latency, up to 10 seconds per read.
Low CPU usuage, I suspect this is because of the High Disk queue length, and the CPU is simply waiting.

We're running a 24 Core machine, INtel Xeon 6128 (6 cores per socket, 4 CPU's total). We currently got 680 Gb allocated to the VM, with 660 Gb avaiable to SQL.

We're patched to the latest CU, and Windows Patches are more or less up to date.

We're running on a very performant SAN, and haven't seen any IO issues from the SAN point of view that are affecting things.

The only solution we currently have, is to restart the entire machine. Even restarting the service doesn't make a difference. When SQL is stopped disk IO is fine, but once it is restarted (without a server restart), the queue length and latency all go down the toilet.

My current suspicion is around the use CLR integrations and the memory utilisation/garbar colleciton on these, but I haven't been able to find a way to accurately spot this.

I'm at a complete loss of where else to start looking, but currently we're having to restart our server almost weekly due to the issue.


texts: SQLServer: Storing File Extension and normalization?

texts:
After much research, I've decided to store pdf/img/txt/doc/etc files on a web server instead of a database. However, the reference to that file will be stored in SQL Server.

So far I have...

| File_id (int) | FileName (varchar(260)) | Uploaded Date (Datetime2) | File Extension (varchar(4)) |
|---------------|-------------------------|---------------------------|-----------------------------|
| 1             | Billing Invoice         | 2019-10-20 etc            | pdf                         |
| 2             | Receipts                | 2019-10-20                | jpeg                        |
| 3             | Orders                  | 2019-10-21                | txt                         |

My question is, should I store the file extension as is or create an extra table for lookup and use foreign key constraints?

| File_id (int) | FileName (varchar(260)) | Uploaded Date (Datetime2) | File Extension (FK to extension_id) |
|---------------|-------------------------|---------------------------|-----------------------------|
| 1             | Billing Invoice         | 2019-10-20 etc            | 1                         |
| 2             | Receipts                | 2019-10-20                | 2                        |
| 3             | Orders                  | 2019-10-21                | 3                         |

| Extension_id (tinyint) | Extension (varchar(4)) |
|------------------------|------------------------|
| 1                      | pdf                    |
| 2                      | jpeg                   |
| 3                      | txt                    |

I'm a pretty junior software engineer and I'm worried that over-normalizing might cause performance/readability problems later on or just cluttering the database by adding unnecessary tables.


texts: Identifying why query is sleeping with open transactions

texts:
We have a 3rd-party application which is sporadically leaving connections in a sleeping state with an open transaction. This is causing many other queries to be blocked. I've been logging sp_WhoIsActive to a table, and below is the lead blocker. I believe the Java application is using Hibernate ORM.

The query is against a very small table with just a few thousand rows. If I run this statement manually, it finishes immediately. The query in my sp_WhoIsActive table shows NULL in the wait_info table, and 10 million reads, which sounds suspicious. I force statistics updates every day on the entire database as we've seen hundreds of millions of reads on similar small queries in the past. That seems to have helped, but this still doesn't sound right.

How can I troubleshoot this to see what's happening? The record does not show the execution plan.

(@P0 decimal(38,0),@P1 decimal(38,0),@P2 nvarchar(4000))DELETE FROM
  SCHEDULE WHERE (PROCESS_ID= @P0 AND PARENT_ID= @P1 AND SCHEDULE!= @P2)