texts: What is the appropriate SQL statement/s to produce a stacked bar chart for multiple random variable distributions?

texts:
The Objective
Graph the difference between the probability distributions of sets of random variables that come from a number of different sources using a stacked bar chart (or another type of graph suited for such visualization).
The Problem
I'm not sure of the right combination of SQL SELECT statement(s)/condition(s) to present the data to the graphing tool in the right way to achieve the objective above.
The Method
Summary
Feed the source data stored in Google Cloud Platform (GCP)'s BigQuery into Google Data Studio and use a stacked bar graph to visualize the numerous probability distributions.
The Source Data
I'm dealing with simple binary outcomes, either a heads or tails. One trial (== one second) per source yields between 0 to 200 how many heads (1s) were counted.
The schema of the data we have stored in BigQuery is as follows:
time      | source1 | source 2 | ... sourceN
---------------------------------------------
09:00:00  | 99      | 110      | 95         |
09:00:01  | 101     | 115      | 107        |
09:00:02  | 99      | 91       | 97         |

For a Single Source
The following works fine if I'm trying to graph the results from just a single source. And as the law of probability would say, the graphed distribution is moreorless in fit with the expected bell curve.
SELECT
  source1 AS src1,
  COUNT(source1) AS count
FROM
  `rnddata`
WHERE
  recorded_at >= "2021-03-11 08:45:00"
AND
  recorded_at < "2021-03-11 09:45:00"
GROUP BY src1
ORDER BY src1

src1 | count
75   | 1
77   | 2
80   | 2
81   | 5
82   | 6
83   | 20
84   | 10
85   | 14
86   | 31
87   | 33
88   | 48
89   | 74
90   | 74
91   | 89
92   | 107
93   | 133
94   | 135
95   | 168
96   | 160
97   | 192
98   | 190
99   | 195
100  | 189
101  | 197
102  | 191
103  | 188
104  | 180
105  | 201
106  | 130
107  | 128
108  | 107
109  | 96
110  | 64
111  | 65
112  | 45
113  | 40
114  | 19
115  | 22
116  | 18
117  | 9
118  | 8
119  | 6
120  | 3
121  | 4
125  | 1

For Multiple Sources
I'm thinking that if I can have multiple dimensions (src1, src2... srcN) with a single count column for the metric, then I will be able to graph the dimensions as stacked bars on the X axis and have the count metric set to the X axis.
I imagine the output could be something like this:
src1 | src2 | count
75   | null | 1
77   | 77   | 2
80   | 00   | 2
null | 80   | 5
82   | 82   | 6
83   | null | 20
83   | 11   | null
84   | 84   | 10

I did a bit of research and experimenting with UNIONS and JOINS and INTERSECTS but couldn't quiet get the above output. Any advice on how to form such an SQL statement to do that, or offer a better presentation (schema) of the data for the graphing purposes, would be greatly appreciated.


texts:
I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu).

My DB is pretty large: ~250GB.

I'm looking for a backup tool/service that will do the following:

full backup
no downtime / locks on the servers
automated as possible

any suggestions?


texts: MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

texts:
If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant performance gain? Are there other benefits to updating a set of records in this way?

For example,

UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 != 'foo';

vs

UPDATE table1 SET value1='foo' WHERE value2='bar';

A more concise example:

+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| Field              | Type                                            | Null | Key | Default | Extra          |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+
| pk_id              | int(10) unsigned                                | NO   | PRI | NULL    | auto_increment |
| user_id            | int(11)                                         | YES  |     | NULL    |                |
| status             | enum('unread','accepted','deleted')             | YES  |     | NULL    |                |
| processed_datetime | datetime                                        | YES  |     | NULL    |                |
| some_column        | varchar(255)                                    | YES  |     | NULL    |                |
+--------------------+-------------------------------------------------+------+-----+---------+----------------+

...with another example query:

UPDATE table1 SET status = "accepted", processed_datetime = NOW() WHERE pk_id = 1234 AND user_id = 5678 AND status != "accepted"

(Note: this is not my code / schema, someone else's that I am working with)


texts: char or smallint for a column with numeric codes?

texts:
I have columns in my application that have values that always have 4 digits, e.g. '0100' or '1230'. One of examples could be Merchant Category Code.
For the time being, I have set the type of this column to char (4). I have many columns of this type. One of these types of columns is also included in nonclustered UNIQUE index. I would like to establish one approach to this type of column.
Is it better to use char (4) or maybe smallint, padding in the application by adding ' ' or  0 to the beginning?
The table in which I have these columns is partitioned and will contain about 300 million records. UPDATE will probably not be executed on columns with this type. They will also not appear in WHERE (maybe sometimes in ad hoc reports).
Which data type should I choose?

It's like a zip code, it can be numeric but you must store leading zeros

Exactly. I am thinking what is better approach in terms of performance. Also have to take into consideration that if I store it as smallint, the application code has to parse it to string and add leading zeros.


texts: Is it possible to prevent SQL Agent Job from running if related job or previous instance has not finished?

texts:
I have three SQL Agent jobs, one for each of three registries being managed. These go off on a staggered schedule every two hours (i.e. Reg 1 at 9am, 3pm; Reg 2 at 11am, 5pm; Reg 3 at 1pm, 7pm).
In most every circumstance, it takes less than two hours for one of these jobs to run. However, if it is taking longer than expected, I do NOT want the other job to run. So, if Reg 1's 9am run is taking more than 2 hours, I do not want Reg 2's 11am job to run UNTIL Reg 1's job is completed.
How can I work this conditional run functionality in? I am not seeing anything in the schedules properties of the job. Let me know if you need more clarification!
Thanks!!


texts: Enforcing maximum number of relations for a record

texts:
I am trying to figure out the way to enforce a maximum number of relations in a highly concurrent system.  The following is an example use case.
// Job table
job_id   | ... | num_positions
xx-xx-xx | ... | 3

// Positions table - only 1 spot left
position_id | job_id   | user_id
aa-aa-aa    | xx-xx-xx | ee-ee-ee
bb-bb-bb    | xx-xx-xx | ff-ff-ff

Now let's say two people try to fill the last spot at the same time. What is the best/right way to enforce that there will only ever be a maximum of 3 records? Ideally, the transaction that fires first fills the spot. What is the best way to implement this sort of functionality? Isolation levels etc?
Thanks in advance


texts: Can't connect to new instance of SQL Server

texts:
I was trying to upgrade to SQL Server 2014 from SQL Server 2012, but I kept getting a failure on one of the pre-installation checks saying that I needed to have SP1 or later installed for SQL Server 2005. Not sure why it was asking this as I'm pretty sure I never had 2005 installed. I went ahead and found the latest SP for 2005 and installed it but still I was getting the same failure on the pre-install check.

So I decided to install a new instance of 2014 along with cumulative update #5. I named the new instance 'MSSQLSERVER14' to tell it apart from the 'MSSQLSERVER' instance that was already installed for SQL Server 2012. So in my Services I basically have two instances of each service of Sql Server. One for 2012 and one for 2014. I disabled all the 2012 services so only the 2014 are running. However, when I try to connect through Sql server management studio it tells me it cannot connect because of a network-related or instance-specific error.

I've been using Sql Server 2012 with Management Studio for that last couple of years with no problems, so I'm 100% this is caused by the switching to 2014. Anyone know how I can solve this issue?


texts: Get more information from Oracle audit trail

texts: I just turned on auditing for an Oracle 11g database. I'm interested in getting more information about each event; for example, when I see a CREATE_INDEX event, I'd like to know which field was indexed. Is there any way to do that?


texts:
I am reading this article: Inside the Storage Engine: Anatomy of a page.

I have a database MyDB, and there is a table MyTable in the database.

I have the following questions:

If I do as follows:

(1) use the following query to find the object ID for a table:

Use MyDB;
select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');

(2) then use the following command to find all pages allocated to MyTable:

dbcc ind(MyDB, 'MyTable', -1);

(3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:

DBCC TRACEON(3604);
DBCC PAGE (MyDB, 1, 17386, 3);

Then in the dumped content (page header) of step (3), the  m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?

And whether that works for both user tables and system base tables, such as sys.syscolpars base table?

Based on my test, the above two conclusion are both correct.
What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’  always equals to m_objId. Why? The original article does not explain the metadata clearly.

I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014


texts: Restore encrypted database to another server

texts:
I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. I'd like to run another copy of the product for testing on another server. I've restored the database to another server and installed the product on that other server. When I installed it I supplied the same password, and then restored a backup from the main server. However I'm getting the error message:

System.Data.SqlClient.SqlException: An error occurred during decryption.

from the product. I can access the tables using SQLServer Management studio.

I have tried this:

on first server:

CREATE CERTIFICATE cert1 WITH SUBJECT = 'Certificate for my stuff'

BACKUP CERTIFICATE  cert1 TO FILE = 'd:\backup\cert1.dat'
WITH PRIVATE KEY
(
ENCRYPTION BY PASSWORD = 'mypassword',
FILE = 'd:\backup\cert1_privatekey.dat'
)

on second server:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

CREATE CERTIFICATE cert1 FROM FILE = 'd:\cert1.dat'
WITH PRIVATE KEY
(
FILE = 'd:\cert1_privatekey.dat',
DECRYPTION BY PASSWORD = 'mypassword'
)

I also tried this on the second server:

alter MASTER KEY regenerate with enCRYPTION BY PASSWORD='password'

but that gave an error message about an asymmetric key.

How can I restore the backup from the first server onto the second server?

Update:

There is an asymmetric and symmetric key in the database as well. If I open the symmetric key using the asymmetric key I get the same error, so I think that's why it's not working - somehow the keys aren't transferring in such a way that they can be used.


texts:
I have a database to hold data about projects.

Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many relations where 1 project can have many contacts and many images.

However, I need to be able to keep "versions" of projects. So, if a project is updated, the previous data about the project is still present. This is a large issue with many-to-many relationship. For example, there is a categories table, and a project_categories table - many projects can have many categories.

When a project updates, not every piece of data will be updated. For example, if a project gets a new contact, it won't always have a new image, so the images table won't be updated.

One solution I have considered is to have a versions table with an auto-incrementing ID; a project_id column and a time column. Each table will also get a version_id column. Whenever a project is updated, a new row is created in versions. Then, when the tables are updated, the version_id gets entered too. This means that the latest row about a project in project_images might be 3 whereas the latest version in project_contacts might be 27.

Because the version_id is incrementing, it's really simple to get the latest version of a single project, as you can join all the tables by the highest version_id.

Where this idea seems to fail for me is if I want to get the version of a project from a specific date.

Could I use my current solution to address that problem?


texts: Optimizing a query that gets the opening balance

texts:
I have a query like this

select org, `Balance` from oss_collection_history och2
  where och2.`DateFrom` =
    (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)

It works, but with my data set it takes about 4 seconds.

The explain output looks like this

id|select_type       |table|partitions|type|possible_keys                     |key|key_len|ref         |rows|filtered|Extra      |
--|------------------|-----|----------|----|----------------------------------|---|-------|------------|----|--------|-----------|
 1|PRIMARY           |och2 |          |ALL |                                  |   |       |            |6651|     100|Using where|
 2|DEPENDENT SUBQUERY|och3 |          |ref |Org,oss_collection_history_Org_IDX|Org|22     |ca1.och2.Org| 443|     100|Using index|

I tried adding the following indeices, but doesn't appear to improve the performance

CREATE INDEX `oss_collection_history_Charges_IDX3`
ON oss_collection_history (`DateFrom`, `org`);

CREATE INDEX `oss_collection_history_Charges_IDX4`
ON oss_collection_history (`DateFrom`);

CREATE INDEX `oss_collection_history_Charges_IDX6`
ON oss_collection_history (`DateFrom`, `org`, `Balance`);


texts: How to keep off-site developer databases up to date with production?

texts:
We have a production database running on SQL Server 2008 R2 with files stored in it.  We were concerned about the size of the database so we've opted to enable FILESTREAM so the files would be stored on the disk and we could hopefully reduce the size of our backups. I have found that we can exclude the FILESTREAM filegroup from the backups but then we have issues querying those tables upon restore (because the fields are missing data, which is expected). Our next thought was then to possibly sync the files from the disk using automated FTP or dropbox or something of that nature.  I haven't completely explored this yet though.

The real problem we're trying to solve here is keeping our developers up to date with the production database. With the growing size of our database it's becoming more and more of a chore to do a full backup and download the database and it will eventually become prohibitive. I'm wondering what other folks are doing to keep remote devs up to date.

I've taken a quick look at SQL Server's log shipping but that just seems like it will require a lot of custom automation (scheduling FTP and restore scripts) to get it working reliably with a few devs in various geographic locations and I'm not sure how to use the secondary database for development since it would likely be read-only. I'm open to any suggestions and please let me know if I can provide more information about our situation that would help bring out suggestions.

UPDATE: The developers only need a reasonably current copy of the production data. This helps to reproduce issues so they can be resolved. We're okay with the data being a bit stale but keeping the transfer times to a minimum would suggest frequent backups.

Thanks for the suggestions so far, just to add a bit more our database is currently 12GB or so and we expect to double that in the near future with the signing of more clients.  I've run a couple test backups with compression enabled and that saved us about 1-2GB but I think that since most of the bulk is the stored files (now in FILESTREAM) that we're not going to get the drastic reduction we would hope for (though we'll probably still compress to save what space we can). I think we'd like to be able to restore the local copies once or twice a week just to keep tabs on things. I'm not exactly sure what our bandwidth cap is from our hosted server but it seems that several people downloading 20GB files several times a month won't be a great use of whatever bandwidth we have. I don't have any metrics on the transaction log growth/usage but I know we have processes to that lots of deletes/inserts so I can imagine it would need to ship a decent amount.


texts: ALTER TABLE online by keeping data as BLOBs

texts:
Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source

  Additionally, it's crucial to be able to change the schema: either to
  use a database where ALTER TABLE can be done online, work around the
  database's limitations and build a way to non-destructively perform an
  ALTER TABLE, or use extreme de-normalization (store the value as a
  serialized blob along with a version that can be used to infer the
  schema in use at that time). This is probably important for any kind
  of a real-time database driven  application, but crucial for social
  networks where new features (e.g., new profile fields) are added all
  the time.


texts:
My problem:

I'm designing a system, that will have multiple administrators with different administrator levels (level 1,2, ...). When a user logs in and creates something (eg. organisation), all of the administrators need to confirm what the user created. The administrator with administrator level 1 is the first one to confirm. If he accepts the users data he will pass the decision to administrator with administrator level 2. He will the again decide if the user data is correct or not. This process goes on until the administrator with the highest administrator level is reached.

My proposed solution:

Every administrator would have a column that indicates his administrator level. User data table would then have a confirmation status column and a column, indicating which administrator level was the last one to set the confirmation status.

What do you think?


texts: Design database for real estate property management?

texts: I have design this database diagram to real estate property management just I want to see if I get it right especially the relation between properties and finances because I made it one(property) to many(finances"bank loans")?


texts:
SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column.

e.g:

create index pt_spgist_idx on geo using spgist(point);

'Point' column on table 'Geo' is Point type of Geometric Types.

and we can use sql sentence to find the nearest point of (34.34898,-92.82934) with spgist index 'pt_spgist_idx':

select point from geo where point ~= '(34.34898,-92.82934)'

We can see that sp-gist index supports Geometric Point type.

The question is:

Does spgist extension in PostgreSQL 9.2.3 support float or int array? For I want to use sp-gist index in int array whose size is 500.


texts: impdp import fails with object already exists in oracle 10g?

texts:
I exported the dump using EXPDP command. After exporting I dropped the user and the associated tablespaces by logging is as / AS SYSDBA.

Then I re-created the User and the Tablespaces. Then I imported the dump using IMPDP.
After importing I tried logging in with the re-created user and tried to query a table. And I got the following error.

ORA-01115: IO error reading block from file 5 (block # 326667)

ORA-01110: data file 5: '/data01/test/TBS_MYTBS_01.dbf'

ORA-27091: unable to queue I/O

ORA-27072: File I/O error

Additional information: 4

Additional information: 326666

Machine Details : Sun Solaris
Oracle Version  : Oracle10g

Could someone help me in resolving the error?


texts:
I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one.

Our current situation is ~100 GB of InnoDB and MySAM data in 10 DBs (largest single ~60GB) with no replication. Nightly mysqldump backups by AutoMySQLBackup lock the database for 20 minutes.

We removed Percona XtraBackup due to difficulty in restoring the databases to test if the backup was actually viable and if we could recover from a disaster with them. Also we often transfer several of the databases to a different location for development testing. It didn't appear easy (or even possible?) to restore a single database from the XtraBackup dump on our Red Hat server to a Mac OS X machine for local development. Of course it is very easy to do that with a mysqldump.

Our new setup will have a MySQL 5.5 master replicating to a MySQL 5.5 slave on a different server in the same rack. We're looking to have daily backups, no downtime due to locking etc, and be able to move the backups around easily and restore on different servers/platforms. Perhaps this solution suggested by the prolific Rolando is best?

  SUGGESTION #2 : Use MySQL Replication on the Different Server

  In consideration of your first need (Low Cost), if you have access to
  a commodity server with adequate diskspace, setup MySQL Replication to
  that external server. That way you can run backups on the Slave as
  follows

  STOP SLAVE;
  XtraBackup
  START SLAVE;

  You can do this with zero impact (no server load, no disk I/O) on the Master

Naturally we would replace the XtraBackup with a mysqldump. Are there any better ideas for our situation?


texts: repmgr 3.0.4 and postgres 9.4 on ubuntu (ERROR: replication slot "repmgr_slot_2" does not exist)

texts:
I'm not a DBA but I have been tasked with setting up a database and streaming replication and repmgr is the tool I have decided to use.

The master goes on fine and I can see it registered in the cluster.

The error on the slave is as follows

[2016-07-22 15:39:35] [NOTICE] using configuration file "/etc/repmgr/repmgr.conf"
[2016-07-22 15:39:35] [NOTICE] destination directory '/var/lib/postgresql/9.4/main' provided
[2016-07-22 15:39:36] [ERROR] aborting, remote host xx.xxx.xxx.xxx is not reachable.
[2016-07-22 15:39:36] [ERROR] unable to drop replication slot "repmgr_slot_2":
ERROR:  replication slot "repmgr_slot_2" does not exist

[2016-07-22 15:39:36] [ERROR] unable to take a base backup of the master server
[2016-07-22 15:39:36] [WARNING] destination directory (/var/lib/postgresql/9.4/main) may need to be cleaned up manually

The command casuiging the abover error is

repmgr -f /etc/repmgr/repmgr.conf --force --rsync-only -h dbhost1 -d repmgr -U repmgr --verbose standby clone

The config file is as follows

cluster=test
node=2
node_name=db-002
use_replication_slots=1
conninfo='host=dbhost2 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.4/bin

I have tried loging into the dbhost1/masrer from dbhost2/slave using

psql -h xx.xxx.xxx.xxx -p xxxx -d repmgr -U repmgr

and it works just fine so the pg_hba file in dbhost1/master is configured correctly using trust for authentication.


texts: How can I identify correlated subqueries that can not be rewritten as JOINs without using DISTINCT?

texts:
I am fairly new to SQL query tuning. I have been trying to understand how to write equivalent queries. While going through Prof. J. Widom's Stanford online video lectures, she makes mention that some subqueries that cannot be written by JOINs without the use of DISTINCT. For example, see this:

4:45 / 20:13 - GPA example

SELECT GPA
FROM Student
WHERE sID in (select sID from Apply where major = 'CS');

6:39 / 20:13 - students applied to CS but not to EE

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
  AND sID NOT IN (select sID from Apply where major = 'EE');

My question is how can one know if an SQL statement written using  subqueries will have an equivalent one written using joins. I am quite comfortable, if in the answers, someone likes to use relational algebra notation.

I searched quite a bit on the Net and could not find a suitable answer.

Sample Data

Schema and table creation (for PostgreSQL) are as follows,

CREATE TEMP TABLE college AS
SELECT cname::text, state::text, enrollment::int
FROM ( VALUES
  ('Stanford', 'CA', 15000),
  ('Berkeley', 'CA', 36000),
  ('MIT', 'MA', 10000),
  ('Cornell', 'NY', 21000)
) AS College(cname, state, enrollment);

CREATE TEMP TABLE student AS
SELECT sid::int, sname::text, gpa::real, sizehs::int
FROM ( VALUES
  (123, 'Amy', 3.9, 1000),
  (234, 'Bob', 3.6, 1500),
  (345, 'Craig', 3.5, 500),
  (456, 'Doris', 3.9, 1000),
  (567, 'Edward', 2.9, 2000),
  (678, 'Fay', 3.8, 200),
  (789, 'Gary', 3.4, 800),
  (987, 'Helen', 3.7, 800),
  (876, 'Irene', 3.9, 400),
  (765, 'Jay', 2.9, 1500),
  (654, 'Amy', 3.9, 1000),
  (543, 'Craig', 3.4, 2000)
) AS Student(sid, sname, gpa, sizehs);

CREATE TEMP TABLE apply AS
SELECT sid::int, cname::text, major::text, decision::text
FROM ( VALUES
  (123, 'Stanford', 'CS', 'Y'),
  (123, 'Stanford', 'EE', 'N'),
  (123, 'Berkeley', 'CS', 'Y'),
  (123, 'Cornell', 'EE', 'Y'),
  (234, 'Berkeley', 'biology', 'N'),
  (345, 'MIT', 'bioengineering', 'Y'),
  (345, 'Cornell', 'bioengineering', 'N'),
  (345, 'Cornell', 'CS', 'Y'),
  (345, 'Cornell', 'EE', 'N'),
  (678, 'Stanford', 'history', 'Y'),
  (987, 'Stanford', 'CS', 'Y'),
  (987, 'Berkeley', 'CS', 'Y'),
  (876, 'Stanford', 'CS', 'N'),
  (876, 'MIT', 'biology', 'Y'),
  (876, 'MIT', 'marine biology', 'N'),
  (765, 'Stanford', 'history', 'Y'),
  (765, 'Cornell', 'history', 'N'),
  (765, 'Cornell', 'psychology', 'Y'),
  (543, 'MIT', 'CS', 'N')
) AS apply(sid, cname, major, decision);


texts: How to get last 12 months values when some months have no records in the table

texts:
I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data -

The requirement is to add the month columns for which there is no row with 0 as Total for last 12 months (i.e from 2018-11 to 2018-12). The expected output should be:

Kindly help me with the query formation?


texts: Joining Multiple Tables with 2 aggregated tables

texts:
I'm using mySQL 5.7
I have seen this question a couple times and it seems like the answer was followed exactly but only my first row of data is correct.

I'm trying to join 3 tables 2 of them are aggregated. The sum of listed quantity
is correct on the listings table.

Select Sum(listings.Quantity) As Cnt, product.ProductId
from product
left join listings on listings.ProductId = product.ProductId And
ProductTypeId = 4
Group By product.ProductId
order by ProductId asc;

Then the next table is a sum of the sales using the same id from the product table. I get the correct sum.

Select Count(sales.SalesPrice) As NumSld,Sum(sales.SalePrice) as Sld
, ProductId
from product
left join sales on sales.ProductId = product.ProductId And
ProductTypeId = 4
Group By product.ProductId
order by Sld asc;

Here is the attempted Join of the tables

Select t1.Cnt, pro.ProductId, t2.NumSld, t2.Sld
From product pro
Left Join (
          Select ProductId, ProductTypeId, Sum(Quantity) As Cnt
          From listings
          Group By ProductId
          ) t1 on t1.ProductId = pro.ProductId and t1.ProductTypeId = 4

Left join (
           Select ProductId, ProductTypeId, Count(SalePrice) as NumSld,
           Sum(SalePrice) As Sld
           From sales
           Group By ProductId
           ) t2 on t2.ProductId = pro.ProductId and t2.ProductTypeId = 4
Order by t2.Sld desc;

The data returned on this is incorrect. Only the first row is correct. I think its something simple that I'm missing. I have been stuck on this for days. Can anyone figure it out?


texts: Are updates the only disadvantage of denormalization?

texts:
I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table):

I have read that the problems that you may face when using denormalization are related to the operations of INSERT, UPDATE, and DELETE.

Now I understand why we have the UPDATE problem, because if I updated one piece of data, then I would have to update the rest of the related pieces of data (for example: if I updated the student name "Paul" in the Student table, then I would have to also update the student name "Paul" that exist two times in the School_has_Student table), but I don't understand why INSERT and DELETE are also problems.

Note: I know that the increase of storage space is also a disadvantage, but I do not think it is a big disadvantage with current storage devices which have very large capacity.


texts: How to get a list of postgresql indexes that have not been used in any queries recently?

texts:
Is there a way to get a list of the indexes that have not been used by any queries since some arbitrary cutoff time in the past? Or more simply, a list of all indexes along with a timestamp of when each one was last used in the execution of a query?

What I've tried so far

I've read the Wall Of Text at https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW, but a solution is not obvious (I'm mostly a MySQL user and I find PostgreSQL documentation very unclear, lost in the details).


texts: MySQL DATE vs DATETIME read/write performace

texts:
Did anyone perform accurate read/write performance comparisons of MySQL's DATE vs DATETIME column types?

Please do not bother writing "It should be faster" or "I think it's faster". I am looking for accurate performance measures and configurations under which it has been performed.


texts: MySql Multi Master Active Active Mutli Write

texts:
I am establishing Multi Master Replication [All mater can write to their database], Initially I started with two server. After establishing connection and sync data on both servers. There are some questions which are bothering me like

If there is connection lost between two server and at that time both updates the same row or create a row with same primary. There will be collision between them and sync will break.
Can we implement same things for Multiple Master configuration?
Is there any monitoring and conflict resolution tools which can I use?


texts: ORA-16000 when trying to perform select on read only Oracle database

texts:
My application's SQL encounters an ORA-16000 error when trying to access a read only Oracle database.

ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE",
line 272 ORA-06512: at line 1 ### The error may exist in com/xyz/report/dao/Transaction001Mapper.xml

This is the query that involves the XMLTYPE, the INTERFACE_CONTENT is a CLOB column:

SELECT CONTENTS FROM ERRCODES WHERE
       CODE=(SELECT xmltype(INTERFACE_CONTENT).extract('/Message/PaymentStatus/ps:FIToFIPmtStsRpt/ps:TxInfAndSts/ps:StsRsnInf/ps:Rsn/ps:Prtry/text()','xmlns="urn:abcde" xmlns:head="urn:iso:std:iso:20022:tech:xsd:head.001.001.01" xmlns:ps="urn:iso:std:iso:20022:tech:xsd:pacs.002.001.03"').getstringval() APP_CODE
FROM MESSAGE_EXTERNAL_INTERACTION MEI WHERE MEI.MSG_TYPE='Pacs_002'
    AND MEI.MID='MY_ID')

I also did a lot of EXTRACTVALUE() method on an XML field type.

The SQL is working perfectly if the database is not read only (read write).

What is the issue here? Is this related to some missing privileges/grants?


texts: How to work with multiple Postgres databases in psycopg2

texts:
I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure out a way how to do that using psycopg2.

So I was thinking I probably need two different cursors:

conn_local = psycopg2.connect(dbname='local_db', host='localhost')
conn_remote = psycopg2.connect(dbname='remote_db', host='some.other.server')

curs_local = conn_local.cursor()
curs_remote = conn_remote.cursor()

But how can I address those databases? For instance, when I try to join data from both tables:

curs_local.execute("""
    CREATE TABLE local_db.public.newtable AS
    SELECT remote_db.public.remotetable.rcolumn AS col_from_remote,
    local_db.public.localtable.lcolumn AS col_from_local
    FROM remote_db.public.remotetable, local_db.public.localtable""")

There will be an error in the style of psycopg2.NotSupportedError: cross-database references are not implemented: "local_db.public.new_table". The ATTACH TABLE command (as described in the solution here) does apparently not exist in Postgres / psycopg2.

Is it possible to work with multiple databases at a time? How?
Or will I have to copy (export / import) the data from remote_db to local_db first?


texts: How do I perform daily, monthly SQL Server 2005 maintenance?

texts:
Team,

I have SQL Server 2005 and I am new to DBA role and wondering if there is any T-SQL Script/Stored Procedure out there that they will help me accomplish the following on regular basis - Daily, Weekly, Monthly Yearly.

Hardware Utilization and Performance
(Memory, Available Disk Storage, and then CPU Utilization)
Database and Server Configuration
(Database Settings, Database Status, Database Back Up Status - Completion, Max
Server Memory Settings)
Database Maintenance
(SQL Server Logs, File Growth, Mirroring Monitor , Back up completion, Index
Fragmentation)

Thank You.


texts: How to add column/alter structure to big table in MySQL

texts:
'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried:

Alter table in screen with table lock. Took ~30hours and nothing. So i just stopped it. First i made mistake cause didn't end all transactions but 2nd time was no multilock. Status was copy to tmp table.

Since i also need to apply partitioning for this table we decide to make dump, rename and make table with same name and new structure. But dump is making strict copy(atleast i didnt found something else). So i added to dump a new column with sed and query it. But some strange errors began. I believe it was caused by charset. Table in utf-8 and file became us-ascii after sed. So i got errors(unknown command '\'') on 30% of data. So this is also a bad way.

What are other options to accomplish this and speed performance(i can do it with php script, but it will took ages). What will be performance of INSERT SELECT in this case.

Thanks for any advance.


texts: How to use keys in a table so two columns are unique

texts:
I want to create a basic database that handles photos. Also, i want to show  a photo of the day to the user.
I´ve created a table called ´photo´ and another table ´photooftheday´, like so

CREATE TABLE `photo` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`caption` VARCHAR(255) NOT NULL,
`filename` CHAR(29) NOT NULL,
PRIMARY KEY (`photo_id`)
)

CREATE TABLE `photooftheday` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL,
`date` DATE NOT NULL
)

I didn't add an auto increment column for the table photooftheday, coz I believe it’s not necessary (maybe I’m wrong).

My question is about the table ´photooftheday´. I don’t know how to handle keys so each day contains a photo that is not already selected in another day. In this case, i’m confused about primary keys, unique keys, keys, foreign keys, column indexes...

Also, if it helps, my query to select the photo of the day is going to use the date column, like:

$todays_date = date('Y-m-d');

$q = "SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE DATE(pd.date) = '$todays_date'";

How do i handle the columns in the table photooftheday, so to optimize it?

Thanks


texts: How to prevent a record from being affected by the ORDER BY RAND statement in MYSQL

texts:
I have a series of problems all which I can't put in the title but I will try to explain them as best as I can. I am building an online exam platform with PHP and MySQL.

Problem 1: Prevent a record from being randomized.
So I have a series of MCQ questions with each question having 4 options. I have these questions and answers randomized for each student so no two students have the same pattern of questions. I am using the ORDER BY RAND() command to archeive this. My problem now comes for questions like this...Example:
***Question: Which of the following is an input device?
A) Monitor
B) Speaker
C) Printer
D) None of the above.

My problem is when the system randomizes these answers the option "D" (None of the above) could change to A or B or C which makes the answer invalid since there are answers below it. So is there a workaround which I can use to prevent randomization for option D only while A, B and C are randomized?

Problem 2: Displaying n questions at a time.
For MCQ questions where the answers of one question is to be used in the calculation of the next. So let's say I have an MCQ test of questions and question 1 to 5 are related....how can I make the system display these five questions at once instead of 1 question per screen.
Again how can I let the user be the one to choosr how many questions are displayed at a time...maybe the user wants n questions displayed at the time such that the value of n is the number of questions to be displayed for each screen and a third option where all the questions are displayed at once.


texts:
Is there any way to check whether a table or index has been fragmented?

How to check if table is fragmented or not?

How to check if index is fragmented?

I searched Google but didn't get satisfactory answers. Some points like explain plan, table fragmentation, index rebuild etc I got. But what should be our first step and how should be resolve. How step by step we can move forward to resolve the issue?


texts: "SSIS Evaluation Period Has Expired" on Dev Instance?

texts:
I recently started receiving the following error on a dev server. At one point SQL Server 2016 CTP was installed here but we've upgraded since then. I've tried uninstalling / reinstalling SSIS as well as repairing it. Reboots occurred along the way. For the life of me I can't figure out why this error persists.

I've verified that the DTExec.exe is located where SQL Server expects it to be, using:

SELECT * FROM msdb.dbo.syssubsystems

...and that it's not referencing some other version of DTExec.exe previously installed on the server.

Is there anything else I can attempt to clear this alleged evaluation period?

  Executed as user: (Domain\SQLServiceAccount).
  Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved.
  Started: 11:00:01 PM
  Error: 2017-01-03 23:00:54.81 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Error: 2017-01-03 23:00:57.39 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Could not execute package because of error 0xC0000033.

The rest of the instance is fine. It is only SSIS that is producing the "evaluation period expired" error message.

Maybe someone have accidentally used an Evaluation Edition to set up SSIS? Perhaps, but you'd think an uninstall and subsequent reinstall (using a dev edition install kit) of the service would correct that problem if that were the case.


texts: Database system to store lots of large dictionaries

texts:
I have an application requirement to store a large amount of dictionaries in a database for fast reading. I know beforehand the approximate size and number of data that needs to be stored. The requirement is as follows:

Store around 100.000 dictionaries or hash tables (for fast access to each value in dictionary)
Each dictionary/hash table can have up to 10.000 key/value pairs

Why am I thinking of dictionaries? Because I need lightning fast reads, I can cope with slow writes, as the writes are going to happen offline, but data needs to be read as fast as possible (used for a search engine). My first thought was to use a key/value store, because each dictionary has a unique keyword associated to it. The following is an example of the data that needs to be stored and read efficiently:

"house": {
             "12345322": 0.467,
             "33627312": 0.657,
             "88349823": 0.342,
             ... (up to 10.000 unique keys here)
         },

"car": {
           "12345322": 0.378,
           "33627312": 0.851,
           "88349823": 0.266,
           ... (up to 10.000 unique keys here)
       },
... up to 100.000 dictionaries (unique keys)

Currently I am looking for the right tool to solve this problem. What database system do you recommend? I have a pair of tools that come to mind (Redis) but I do not know if they are adequate for this amount of data.


texts: Summation query with complex grouping criteria

texts:
I have a database of coded actions with this structure.

{User, Date, ActionCode}

I need to generate reports based on dates and action codes, and the action codes are not clean because we have to import them from multiple sources.

I can handle most of them (like the average number of times users perform ActionCode 13).

But, here's what I'm having trouble with: The average number of times users perform an action that can be defined by any one of the action codes 61, 62, 700, or 701. This action can also be defined by using both action codes 84 and 85 or by using the three action codes 10, 11, and 12.

Here is what I have that handles the first set of action codes:

select
  average(cnt)
from
(
  select
    count(distinct(date)) as cnt
  from
    codes
  where
    actioncode in (61, 62, 700, 701)
  group by user
)

I use distinct date because some users code to multiple places and we get back more than one code for the action. How do I add "or they had both 84 and 85 on the same date"?


texts:
I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record.

If I set ALLOW_SNAPSHOT_ISOLATION will it block for a period of time proportional to the record count, or will the data be updated asynchronously?

Primarily, I want to be sure that my database will not be out of service for hours when this setting is enabled.


texts: SQL 2012 Standard Edition (on Azure VM) + Bitlocker. Good or Bad idea?

texts:
I will start this question with a few statements to negate any "Why" questions that are going to be asked.

The Head of IT wants the data encrypted.
Our profession is such that encrypted data is sensible.
Enterprise Edition is an option, albeit a costly one.

We are looking at moving one of our user groups into Azure hosting (RDP, Files, Database, etal). Some members of the department are concerned that as Azure is a public cloud it is possible (although very improbable) that our data could be accessed by another party.

So at rest data encryption has been mooted; I would be happy to use SQLs native TDE, but that is an extra cost (and not a small one) so other options have been looked at.

The Systems Admin has put the idea of bitlocker forward; I know that SQL can be installed and run in a bitlocker encrypted drive. However is it a good idea; has anyone got any experience of using a enterprise level database on a bitlocker encrypted drive?

The sort of answers I am after will look at the performance, safety of both the data and the system; and perhaps suggest other options.


texts: Need some insights on 'index_page' variables in Innodb_Metrics Table

texts:
I was experimenting with page-split behaviour for sequencial vs random ordered inserts in Mysql,Innodb.

For sequencial Inserts I got:

index_page_splits:           3199
index_page_merge_attempts:   0
index_page_merge_successful: 0
index_page_reorg_attempts:   0
index_page_reorg_successful: 0

For random Inserts I got:

index_page_splits:           4423
index_page_merge_attempts:   1
index_page_merge_successful: 1
index_page_reorg_attempts:   1
index_page_reorg_successful: 1

I am having two questions:

1.Why there are increments in index_page_merge_attempts,index_page_merge_successful,
index_page_reorg_attempts,index_page_reorg_successful variables?

2.What is the difference between attempts and successfull variables?


texts: Shall I use JSONB for integers based array?

texts:
I need to store a list of ids in array per each row.
I thought to use JSONB, then store it as : {ids: [1, 2, 3, 4]}.
Then, I found Array type, that I can use to store ids directly into.
The question is, what is the recommended data type for my case?


texts: Is it possible to log backup results to table in a different database on a different server

texts:
I am using Ola Hallengren's maintenance solution to back up a SQL 2012 Express instance in our environment. I am a brand new DBA, only 10 weeks old so I am not too familiar yet but I am learning fast. I am pretty sure I have everything configured the way we want it. I created the stored procedures in the master database of our express instance and I have scheduled batch files to be executed nightly from within Windows Task Scheduler and all seems to be set up and working minus one detail, the logging to table.

First of all, I am not quite sure how to enable it or what database to find the table in if it is enabled?

Second, I would like to log the results to a table in a different database on a different server\instance. Is this possible? If so, does anyone have any direction they can point me to so I can set this up?

Thanks!


texts:
I'm working through the book SQL Cookbook and there's an example where the author uses a query which is purely cosmetic (it's after the first union all statement):

select
    ename as ename_and_dname,
    deptno
from emp
where deptno = 10
union all
select
    '----------',
    null
from t1
union all
select
    dname,
    deptno
from dept;

(The table t1 doesn't seem to have been defined yet in the text, but let's just assume the table exists and the query produces a single row.)  It seems like it could be useful in SQL to have a kind of "empty table" that's a member of every database for performing such queries.  In any case, what's the best way of doing this kind of thing?


texts: Step by step establish a connection between SQL Server 2014 and Oracle 12c

texts:
I have two servers (both servers are on Windows OS):

Server_A running Oracle 12c
Server_B running SQL Server 2014

I want these servers to be able to see each other, so I can SELECT anything I want in Server_A from Server_B and vice versa.

I only want the ability to run SELECT queries (not INSERT, UPDATE, DELETE or any DDL queries), and also if possible to EXECUTE procedures.

What options or technologies we have for achieving this issue and which one is better?

Tell me if any software I have to install or any configuration have to be set on each of servers.
I appreciate explaining the instructions step by step.


texts: Is there an easy way to copy one database to another

texts:
In MySQL?

Say I have a local database. Now I want the remote database to mimic the local database.

One way I can think of is to simply dump the database, upload and let things work.

Well, database is large.

Another I can think of is connecting straight to the database and fill the line one by one with a dedicated vb.net program.

Sometimes I just want to create a back up database in the same server.

How does the pro do so?


texts: Does a user who has permissions to select from a view also need select granted to the children entities of that view?

texts:
If view 1 uses tables A and B, and a user has SELECT granted on view 1 is that sufficient for the user to select from view 1 or does he also need SELECT granted on tables A and B.

Specific details on my situation (in regards to Erik's comment):

I thought I understood permissions, but I'm a little confused right now.

I have a user specifically for testing permissions. I have view 1 in database 1 that references view 2 in database 2 and view 3 in database 2. View 2 in database 2 references table A in database 3. View 3 in database 2 references table B in database 2.

So at the individual entity level, I slowly was adding permissions as necessary and check with my test user to see what error I get back from SSMS.

I first granted select permissions to the test user on view 1. Then based on the errors I was getting, I also needed to grant select on view 2 and table A (that is referenced by view 2). But I did NOT need to grant select permissions to my test user on view 3. This is where I'm confused.

I then assumed my test user might be part of a role (or something) that gives it full select access on database 2, but then if I try to select directly from view 3 it gets a permission denied error, even though it CAN select from view 1 (which references view 3).

TL;DR: Why can my test user account select from a view but can't directly select from one of the entities that that view is dependent on?...is that normal permissions behavior?


texts: Can MSSQL CDC be used to capture old committed transactions?

texts:
I can successfully capture change data from mssql CDC by issuing the query

cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn ,
 '<row_filter_option>' )

Here, from_lsn is the min_lsn(oldest uncommitted transaction) which I can obtain from the Database.

But I have an already committed old transaction which I want to replicate and so I obtained LSN for one of its rows and tried to issue the above query using the obtained LSN (from_lsn) which failed with the following error message

Failure in executing queries  SQLException;ErrorCode : 313;SQLCode : S0003;SQL Message : An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

Is it possible to capture past changes from MSSql ? If yes, some guidance would be appreciated


texts: What to monitor (key indicators) for MySQL server

texts:
I have installed Munin for monitoring my MySQL database server. It enabled a large amount of MySQL plugins out of the box. Since running these plugins can be CPU expensive I want to limit them to the ones that I actually need, and that is going to tell me something usefull.

Binary/Relay Logs
Command Counters
Connections
Files and tables
InnoDB Buffer Pool
InnoDB Buffer Pool Activity
InnoDB Insert Buffer
InnoDB IO
InnoDB IO Pending
InnoDB Log
InnoDB Row Operations
InnoDB Semaphores
InnoDB Transactions
MyISAM Indexes
Network Traffic
Query Cache
Query Cache Memory
Replication
Select types
Slow Queries
Sorts
Table locks
Temporary objects

So my question is; which of these parameters are key indicators and has any value to monitor?


texts: Building dependence between regional data (Street -> City -> PostalCode -> Country)

texts:
I'm in the process of designing a database to contain regional data to be used inside a CRM system.

So, a user suspects that things like the city or country are automatically filled (or maybe just suggested) when he types in certain pieces of information.

Now my question: Is this feasible when using international schemes? Is it possible to get a mapping between PostalCode -> City -> Country in Germany, like I can do in the US or Japan?

How to design such a database with normalization? I already found offers like GeoNames is doing, but they're not normalized let alone referenced via FK constraints.


texts:
I have a weird replication problem I have not seen before.  It's basic mysql replication with single master and two slaves.  One of the slaves is not executing replication events and seems just stuck at some point yesterday.  The other is current.

Running show slave status on the problematic slave shows no increases in any of the counters.
It lists both the IO and SQL threads as running.
Seconds behind reports 0.
None of the log counters are increasing.
No errors are reported.  Running stop/start slave return no errors.  Bouncing the server reports nothing out of the ordinary in the .err log and says it's picking up replication from the relay log position it's stuck on
The master shows the slave as connected
The slave shows two system user replication threads reporting "Waiting for master to send event" and "Slave has read all relay log; waiting for the slave I/O thread to update it".  Their Time counters in the process list are just steadily increasing.
Attempts to connect from the slave w/ the replication credentials to the master via commandline client work fine
There is plenty of disk space in both the datadir and logdir
The Master_log_file it's reporting still exists on the master according to both show binary logs and looking at the actual filesystem (it wasn't pruned or manually deleted from the FS)
The master and both slaves are running the same percona build (5.5.29-30.0-log) and have been as such for many months.

I'm at a loss on what to further troubleshoot. Help?


texts:
The title says it all. In Oracle, we can easily get the OS user using:

select sys_context( 'userenv', 'os_user' ) from dual;

I usually use that for logging purposes (know who inserted/updated what and when).
I searched for something similar for Postgres (I am new to it) but I could not find. Isn't that possible?

In general, if Postgres does not allow me to get the OS user, does it allow retreiving any kind of information outside the DB itself (e.g.: IP of host, .. etc.)?


texts:
I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will be 1.2 TB.

This SHRINKFILE process must fit within a 7 hour maintenance window PER database.

However the DBCC SHRINKFILE on each is taking MANY hours to run. For example shrinking an 800 GB database down to 120 GB takes over 15 hours.

Unfortunately that exceeds my maintenance window and I'm forced to kill the process hoping the database does not become corrupted (subsequent DBCC CHECKDB shows they are fine).

I can see the DBCC SHRINKFILE does NOT fully utilize the available Disk I/O nor CPU resources.

For example, it is possible to copy the full sized database files from one disk to another within only a few hours, but the SHRINKFILE process takes considerably longer.

NOTE: the databases are all set to SIMPLE recovery mode and AUTO_SHRINK is off.

Yes, I see MANY people say to never do this as it destroys indexes - I do plan to rebuild the indexes when the SHRINKFILE completes.

Is there any way to increase the priority of the SHRINKFILE commands or any alternate solutions? Here are some options I'm considering:

SET DEADLOCK_PRIORITY HIGH in the same session before running the
SHRINKFILE.
(But I doubt this will help.)
Creating a new database and copying all the data table-by-table.


texts: PostgreSQL 11 trigger can't select a value in a table to use it in a second table

texts:
Here's a sample of log :

gid alti  numlog
1   79.55 1
2   79.65 2

Here's a sample of us :

gid numlog numus sommet base alti_sommet alti_base
1   1      1000  0      0.25 (79.55-0)   (79.55-0.25)
2   1      1001  0.25   0.39 (79.55-0.25)(79.55-0.39)
3   1      1002  0.39   0.59 (79.55-0.39) ...
4   2      1000  0      0.15 (79.65-0)
5   2      1001  0.15   0.40 (79.65-0.15)

With a trigger on us, I would like to update alti_sommet and alti_base with the result of the math between ().

I create this function just for alti_base for now :

create or replace function activite.calcul_alti_us()
returns trigger as
$body$
 BEGIN
  IF (TG_OP IN ('insert')) THEN
  NEW.alti_base = l.alti-NEW.base from log l where NEW.numlog = l.numlog ;
 return NEW ;
  END IF ;
 return NULL ;
END ;
$body$
language 'plpgsql' ;

and the trigger :

CREATE TRIGGER trg_calcul_alti_us
AFTER insert
ON us
FOR EACH ROW
EXECUTE PROCEDURE activite.calcul_alti_us();

The result : alti_surface = 0 everywhere.
I tried :

NEW.alti_base = (select l.alti-NEW.base from activite.log l where NEW.numlog =l.numlog) ;

I tried with a trigger BEFORE as well.
Both failed.
Thanks for help.


texts: Is there a way to use CREATE TABLE inside of a transaction in Sybase?


texts:
I have an application for a business customer and one of the new components is a time tracking system. The actual time tracking is done (pretty standard schema), but the structure this company uses to price their time is really odd and I don't know how to properly store this.
0-10 minutes: €16
10-15 minutes: €23
...
35-40 minutes: €58
more minutes: €63
In short, it's a completely irregular structure and while I could make a table with a column per interval, that seems to me like a smelly solution. A second table with a foreign key and interval and price also doesn't sound ideal. There should also be multiple of these timetables (different per employee / role).
Does anyone have any advice on storing this?


texts: How Do I Get DB2 NoSQL User-Defined Functions to Work

texts:
I am using NoSQL in DB2 10.5 FP4. I am creating and connecting to the database with the 'db2admin' administrator user ID on my Windows machine. I also used the 'db2 attach to DB2_01 as db2admin' command in the DB2 CLP to make sure that db2admin owns the database, and everything I do is as that ID.

When I start the command line, I get the expected results the first time:

IBM DB2 NoSQL JSON API 1.1.0.0 build 1.3.44
Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 2013 All Rights Reserved.
nosql>Type your JSON query and hit <ENTER>
nosql>Type help() or help for usage information. All commands are case sensitive
Meta-data tables and functions for NoSQL JSON have not been created or have been created incorrectly.
 Please run 'enable(true)' to create them.
 You must have the correct admin privileges.
 Run 'enable(false)' to see the SQL that will be used.
nosql>nosql>enable(true)
nosql>Executing SQL...
Database artifacts created successfully.
nosql>use myw3

It looks like everything worked. However, once I exit and go back into the NoSQL CLI, I get the following error:

NoSQL JSON user-defined functions were not installed correctly or are not functioning correctly. Please check your DB2 installation.
nosql>

If I look at the db2diag log, I see the following:

2014-12-09-16.40.49.036000-300 I2028450H1253        LEVEL: Severe
PID     : 5820                 TID : 7936           PROC : db2syscs.exe
INSTANCE: DB2_01               NODE : 000           DB   : W3DB
APPHDL  : 0-126                APPID: 127.0.0.1.64277.141209214050
AUTHID  : DB2ADMIN             HOSTNAME: dlwester
EDUID   : 7936                 EDUNAME: db2agent (W3DB) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerRoutineResolveAddress, probe:10
MESSAGE : ZRC=0xFFFFFE44=-444
          SQL0444N  Routine "" (specific name "") is implemented with code in
          library or path "", function "" which cannot be accessed.  Reason
          code: "".
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -444   sqlerrml: 67
 sqlerrmc: SYSTOOLS.JSON_BINARY SQL141209160848336 \db2json jsonBinaryOrNull 6
 sqlerrp : SQLERLIB
 sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
           (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)        (11)
 sqlstate: 42724
DATA #2 : String, 16 bytes
jsonBinaryOrNull
DATA #3 : String, 7 bytes
db2json

If I look at the database, I see the JSON_BINARY UDF in the SYSTOOLS schema. I wouldn't worry so much about the warning message when starting the JSON CLI, but I'm also having problems with my application when reading the NoSQL DB. I get the same error as above, except with the SYSTOOLS.JSON_TABLE UDF.

Any ideas what might be going wrong here?

Exception from the application:

com.ibm.nosql.json.api.DBException: [nosql][1.3.44] Routine "SYSTOOLS.JSON_TABLE" (specific name "SQL141209160848438") is implemented with code in library or path "\db2json", function "jsonTable" which cannot be accessed.  Reason code: "6".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.17.31;
 Caused by: DB2 SQL Error: SQLCODE=-444, SQLSTATE=42724, SQLERRMC=SYSTOOLS.JSON_TABLE;SQL141209160848438;\db2json;jsonTable;6, DRIVER=4.17.31
    at com.ibm.nosql.json.api.DBData.update(DBData.java:199)
    at com.ibm.nosql.json.api.DBData.update(DBData.java:220)
    at com.ibm.nosql.json.api.DBCollection.remove(DBCollection.java:1372)
    at com.ibm.nosql.json.api.DBCollection.remove(DBCollection.java:1421)
    at com.ibm.myw3.services.scheduler.source.sourceadapter.SmrtPlntHandlerTest$1.call(SmrtPlntHandlerTest.java:52)


texts: Conditional Join - Specific Column Select

texts:
I have tables like these:

Table Employee ( Emp_Id, Full_Name, ...)
Table Order ( Submitter_Id, Recipient_Id, ...)

Then say that I have conditional join like this:

select isnull(Emp.Full_Name, Emp2.Full_name) as Recipient  -- , other fields
from Order
    left outer join Employee Emp on Emp.Emp_Id = Order.Recipient_Id
    left outer join Employee Emp2 on Emp2.Emp_Id = Order.Submitter_Id
-- where clauses

Brief explaination: The query will return the name of recipient from specific order. An order may has recipient id being set, or using submitter id if the recipient is not set.

The condition: Both table Order and Employee has big amount of records inside, so joining them both is a costly operation. 80% - 90% of records in Order has recipient_id set, so joining to submitter_id can be useless operation. Using isnull as join condition can resulting in index scan (in my experience). Using subquery for column maybe help, but the cost can be high because the operation can be row-by-row.

Is there any conditional join for case like this?


texts: How do I get let SQL Server 2005 know that I changed host name in Amazon AWS server?

texts:
I am using an Amazon AWS SQL Server 2008 R2 server for dev purposes.  In order to follow a new naming convention, we changed the name of the host.   I then tried to let SQL Server know about this server name change, with the usual:

EXEC sp_dropserver '<oldname>'
GO
EXEC sp_addserver '<newname>', 'local'
GO

But then SQL Server complains that server oldname does not exist.

When I do this:
    select @@SERVERNAME

I get back a host name that starts with 'IP-' and is then followed by some hex. Apparently Amazon does some funky DNS aliasing of some sort behind the scenes and comes up with its own internal name, even if I am using oldname and SQL Server itself thinks its oldname.

How do I let SQL Server know that the name of the server is now newname?


texts:
I am creating temporary table during stored procedure execution with the following structure:

[ID] BIGINT
[Point] GEOGRAPHY

the ID is not unique - there are about 200 records for each ID.

I need to find a list with distinct IDs for which there is at least one Point to Point distance larger then constant value (for example 200 meters).

So, I am using something like this:

SELECT DISTINCT DS1.[ID]
FROM DataSource DS1
INNER JOIN DataSource DS2
    ON DS1.[ID] = DS2.[ID]
WHERE DS1.Point.STDistance(DS2.Point) > 200

For 23 000 points, the query is executed for 4-5 seconds. As I am expecting to have more values, I need to find better solution.

I guess that if there is faster way, I can always create a materialized table and implement additional logic that will calculated this on ID base.

I have created a spatial index, but the query optimizer is not using it. If I use a hint like this WITH (INDEX(SPATIAL_idx_test)) I am getting the following error:

  Msg 8635, Level 16, State 4, Line 78
  The query processor could not produce a query plan for a query with a spatial index hint.  Reason: Spatial indexes do not support the comparator supplied in the predicate.  Try removing the index hints or removing SET FORCEPLAN.
  `


texts: sql query to check whether multiple entries exist

texts:
I have a table with the following fileds.Here table name is det
Name ID
A     1
B     2
C     1
D     3

I need a querry to check if multiple entry of name is there for single id. Here iam expecting result like
Name ID
A     1
c     1


texts: I have global autocommits off but I was wondering if it were possible to set autocommit on for PHPMyAdmin during login without doing so manually. Turning off autocommit makes the PHPMyAdmin GUI not work properly (clicking 'delete' on a row won't actually delete it since there isn't a commit). Since PHPMyAdmin creates a new session on each page load, setting autocommit on doesn't do anything since it immediately gets 'wiped out'. I have to manually type in queries followed by a commit.


texts:
I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
    insert into table1(id, value1,value2...)
    select (@id, @value1, @value2...)
 end

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?


texts: Is it Possible to perform Restore on Secondary Replica using Copy_Only Backup that was taken on the same node?

texts:
I'm looking to use copy_only backups taken on a secondary replica, remove that DB from the Availability Group, then restore the DB using the copy_only backup and add it back into the Availability Group. That all works fine but when I try to join the DB back to the Availability Group I get an error.
'Database "AGTest" is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using WITH NORECOVERY.'
This seems like a straightforward error so I do what it says, Perform the same restore but with NORECOVERY and try to add to AG again. Another Error.
'The remote copy of database "AGTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'
This is the part that confuses me, I've tried to restore it with the latest log backups from the primary. No Dice.
Any help or suggestions is greatly appreciated.


texts: Can Cassandra be used to increase database write rate?

texts:
I am developing a database for an analysis job that requires parsing terabytes of information into a database. My job is to create and populate a database and using it to answer analyst's questions. In the course of such questions, we identify deficiencies in the way that the data is being represented in the database. I then have to alter the database structure, which often involves re-parsing and re-reading everything into the database.

The parsing is time consuming, but straightforward to parallelize; since I got a 48-node cluster it doesn't take very long any more. However, I started the project using MariaDB (frankly, no one knew the data-sets would get so big). The big time holdup is now writing into the database: billions of writes every time I modify the database taking over 24-hours with 2 months of collected data. Since we will be gathering data for many more months and years, I can see where the write requirements will soon become prohibitive.

I have to make recommendations about how/if we will expand the database in the future. However, I know next to nothing about distributed databasing. I would like to know: will switching to a distributed database be expected to decrease the write time by increasing the number of nodes to which I can write.

Regarding the data itself, it is designed with an indexed master table (~10^7 rows as of now) and several un-indexed subsidiary tables designed to be joined with the master (~10^9 rows), if that is relevant.

Just so you know, because the database supports strategic analysis and report generation, and not real time information, there is not as much incentive to speed up database read times. Leaving the data un-indexed and accepting long join times is just fine; report generation runs overnight. But the long time for database re-building is starting to chew up whole work days, which is not as fine.


texts: Storing resource information efficiently

texts:
I'm looking for any advice on how to efficiently store some resourcing data for our app.

For each project in our system we need to store the resources required and this comes in the following form:

We store: the stage (and if needed the task) that the resource is associated to, the role they are undertaking, the date period and then either a number of hours per day, days per week or percentage of their time over the date period to be allocated to that user.

Now this may all seem pretty straightforward so far but where the complication lies is with how the data is to be displayed. Users will be able to see the resource plan for a project either by day, week or month. They will also be able to edit the data in any view (we have a textbox per day/week/month). So the data may have initially been entered with a date range or 2015-01-01 to 2015-12-31 but this could be edited in the week view allowing a user to change just one week in the middle of this range to a different allocation percentage. This would mean we would essentially have 3 entries once the update was done.

We have toyed with having an entry per day no matter what the period is they enter it in as this would stop us having to split existing entries when changes are made, but that will mean we will end up with millions and millions of rows of data and this is what we are trying to avoid.

At present it would seem our options are to either have massive amounts of data or have to deal with the complications of splitting existing entries when updates are done using a different date view to what the resource information was originally entered in.


texts:
I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly?

SELECT
  KOS_VER_ID        AS "Vertrag"
  , WHR_ISO_3_CODE  AS "Waehrung"
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN as "Kostenart"
  , SUM (KOS_BETRAG) AS "Summe pro KOA"
FROM
  KOSTEN
  , WAEHRUNG
  , SCHLUESSELTABELLE
WHERE
  KOSTEN.KOS_VERHI_WHR_ID = WAEHRUNG.WHR_ISO_ID
  AND KOSTEN.KOS_KOA_ST_KZN = SCHLUESSELTABELLE.ST_ID
  AND KOS_VER_ID in (2509, 2510, 2511)
GROUP BY
  KOS_VER_ID
  , WHR_ISO_3_CODE
  , KOS_KOA_ST_KZN || '  -  ' || ST_LANGBEZ_EN
ORDER BY
  "Vertrag"
  , "Kostenart"
;


texts: How to understand Composite Primary Key when it comes to storage (in Mysql)?

texts: I can image how rows will be arranged in a single database page as primary key order (example an auto-increment key) after reading this post. But I wonder how it will in the case of composite primary keys. For example consider a pk(a integer, b integer), whether the rows are arranged as per column a or by column b in the database page?


texts: Check if data from database is up to date

texts:
I'm storing some data from database, and want to check if that data is up-to-date from the database version. What is the best way to do this?

My ideas:

Getting checksum from the table and comparing with last update
checksum (must be an overkill)
Getting table modification date and
comparing with current data update time


texts:
I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"...., 'header50' as "header50" from MyTable)
It doesn't work above a certain number of columns, and/or the fixed limit of characters in a query

The goal is to export the headers and the data from the table to a text file or an excel file with a BCP command.

Can you help me out, please ?


texts: MongoDB insertion in two collections or one to improve insertion speed

texts:
I have a collection, in which I have too many records (~300 million).
And the insertion rate is also very high and it takes time to insert documents, which is my main problem.

As I have two types of reports. Lets call them report_type_1 and report_type_2. Currently I insert both these reports in same  collection.
Will it make insertion any faster if I use different collections for these two different types of reports?

Simply, what is faster? Inserting 1000 records per minutes in a single collection or inserting 500 records per minute in two different collections?


texts: Moving data from MS Access to MS SQL Server

texts:
I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of smalldatetime columns. The problem is an error that SQL Server reports when trying to move these tables:

Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

I have a stored procedure which generates dynamic sql to assist with moving each table. I converted all the datetime tables in SQL Server to datetime2 columns, and I added a workaround, or so i thought, to deal with this datetime issue, but it doesn't seem to be working. the stored procedures are kind of bulky so the following is the relevant excerpt:

IF (@Conversion = 1 AND (@ColType LIKE '%date%' OR @ColType LIKE '%time%'))
    BEGIN
        SET @ColumnList = @ColumnList + ', IIF(ISDATE([' + @ColName + ']) = 1, [' + @ColName + '], CONVERT(datetime2, ''12/31/1899'')) AS ['+ @ColName + ']'
    END
ELSE
    BEGIN
        SET @ColumnList = @ColumnList + ', [' + @ColName + ']'
    END

Currently, the actual data that is "bad" (can't be converted) is not really important, so I'm trying to substitute it for an obviously invalid date just to get the process to complete without errors. The MS Access frontend has no code to prevent users from entering invalid dates, so for example if someone makes a typo and creates an order with the ship date of "june 2nd, 202", instead of "2015", MS Access will happily store this and then SQL Server fails to convert that value to a datetime.

EDIT:
To clarify, this procedure is running on a schedule. I can manually repair the bad data now, but if or when more bad data is input by a user, my process will crash again.

As a test, I ran this, and got the same error:

SELECT CONVERT(VARCHAR(10), [DRFINISH], 20) FROM [DB2_JOBS]...[Jobs]

However, this works:

SELECT TOP 100 CONVERT(VARCHAR(10), [DRFINISH], 20) FROM [DB2_JOBS]...[Jobs]

How am I going to convert the data if it causes a crash before it can even be passed into the CONVERT function??

EDIT #2:

These also crash:

SELECT [DRFINISH] FROM [DB2_JOBS]...[Jobs] WHERE ISDATE([DRFINISH]) = 1
SELECT ISDATE([DRFINISH]) FROM [DB2_JOBS]...[Jobs]

Can anyone tell me what I'm doing wrong? I would appreciate it greatly.


texts: how to calculate the exact time taken to do autovacuum in postgres

texts:
it say’s it took 179 hours but how it is possible to spent 179 hours from yesterday ?  Do i am missing anything ?

2019-04-22 07:40:34 CEST [30320]: [4-1] user=,db=,app=,client= LOG:  automatic vacuum of table "xxxx": index scans: 1
    pages: 0 removed, 28757389 remain, 5 skipped due to pins, 2159316 skipped frozen
    tuples: 28764881 removed, 189268339 remain, 5645787 are dead but not yet removable
    buffer usage: 481307580 hits, 107894548 misses, 52708943 dirtied
    avg read rate: 1.307 MB/s, avg write rate: 0.638 MB/s
    system usage: CPU 9467.79s/534895.45u sec elapsed 645049.35 sec

645049.35/60/60 =179.180 hours


texts:
I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City

The problem is,some forms come with incomplete data which is acceptable during the filling of the form which leaves me with data of the form names#Age and the rest of the data is not there.

Since there are many records i want first to process the data with all the four fields with data and i want to use regular expressions to select all those complete records first.

I have tried this query

SELECT text_message from incoming_sms where text_message REGEXP '^([a-z]+$)*[a-z]+$';

but this does not match what i want.


texts:
UPDATE

I've posted a newer more focused question How to use index for simple time range join?
I'll keep this for reference.

ORIGINAL

I have a large (~100 million rows) timeseries table t_16 where the primary key is a field abs_date_time of type timestamp.

I want to join this table to a small (<1000 rows) ranges table, which only has one field range of type tsrange.

In the production example, range is not really a table, but a CTE (i.e. a query in the WITH clause.
I managed to reproduce the issue with a "real" table, like so:

For my tests I create a new ranges table and insert only one range.

create table ranges
(
    time_range tsrange
);

In this case the query is very slow: it makes a full table scan of the timeseries table:

EXPLAIN
SELECT t_16.*
FROM t_16, ranges
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explain output - note the Seq Scan on t_16:

Nested Loop  (cost=0.00..2645938802.76 rows=13056002418 width=80)
  Join Filter: ((t_16.abs_date_time >= lower(ranges.time_range)) AND (t_16.abs_date_time < upper(ranges.time_range)))
  ->  Seq Scan on t_16  (cost=0.00..2098286.16 rows=86400016 width=80)
  ->  Materialize  (cost=0.00..30.40 rows=1360 width=32)
        ->  Seq Scan on ranges  (cost=0.00..23.60 rows=1360 width=32)

When I now analyse the ranges table and run the query again, the index is used and the query is fast:

analyse ranges;

EXPLAIN
SELECT t_16.*
FROM t_16, ranges
WHERE (abs_date_time >= LOWER(ranges.time_range)
    AND abs_date_time <  UPPER(ranges.time_range));

Explain result: note that t_16_pkey is used:

Nested Loop  (cost=127355.89..3147692.66 rows=9600002 width=80)
  ->  Seq Scan on ranges  (cost=0.00..1.01 rows=1 width=22)
  ->  Bitmap Heap Scan on t_16  (cost=127355.89..3051691.63 rows=9600002 width=80)
        Recheck Cond: ((abs_date_time >= lower(ranges.time_range)) AND (abs_date_time < upper(ranges.time_range)))
        ->  Bitmap Index Scan on t_16_pkey  (cost=0.00..124955.89 rows=9600002 width=0)
              Index Cond: ((abs_date_time >= lower(ranges.time_range)) AND (abs_date_time < upper(ranges.time_range)))

Questions:

why is a full table scan used (before the analyse)? Since the join is only on the primary key field, I hoped that this was always used.
Is there a way that I can rewrite the query, so that the index is used, even without analyse (which I cannot use in a CTE)?

Additional Info:

Here is a version that actually uses a CTE:

EXPLAIN
WITH cte AS (
    select * from ranges
)
SELECT t_16.*
FROM t_16, cte
WHERE (abs_date_time >= LOWER(cte.time_range)
    AND abs_date_time <  UPPER(cte.time_range));

This also does not use the index (before analyse), but makes a full-table scan ont the time-series-table.
Explain output:

Nested Loop  (cost=23.60..3527218976.16 rows=13056002418 width=80)
  Join Filter: ((t_16.abs_date_time >= lower(cte.time_range)) AND (t_16.abs_date_time < upper(cte.time_range)))
  CTE cte
    ->  Seq Scan on ranges  (cost=0.00..23.60 rows=1360 width=32)
  ->  Seq Scan on t_16  (cost=0.00..2098286.16 rows=86400016 width=80)
  ->  CTE Scan on cte  (cost=0.00..27.20 rows=1360 width=32)


texts: Setting shared_buffers over 320 MB prevents Postgres from restarting

texts:
I'm running Postgres 8.1 on a CentOS 5 system with 4 GB of RAM. Whenever I set shared_buffers above 320 MB, it won't run.

I've checked SHMMAX and it's at 4294967295 which is the max allowed for a 32-bit/4 GB system. Below are some of the other memory related settings:

max_connections = 225
shared_buffers = 327680
work_mem = 524288
max_fsm_pages = 500000
max_fsm_relations = 10000

Any suggestions on what could improve this?


texts: Cannot re-attach SQL Server Express database to the same server after detaching?

texts:
I migrated a database in a test environment from one server running SQL Server Express 2008 R2 to another running the full version of SQL Server 2012.
I did that by detaching the database from the first server, copying over the .mdf and .ldf files, and attaching those to the new server.
That worked well, except I see some performance losses, hence I want to do some comparisons in terms of speed and maybe execution plans and here starts my problem:
I cannot reattach the original database I left on the old SQL Server Express instance.  I did not do anything to it since it was detached and it worked like a charm until I did.
But now, when I want to attach it to the original SQL Server Express instance, I get this message:

CREATE DATABASE or ALTER DATABASE failed because the resulting
cumulative database size would exceed your licensed limit of 10240 MB
per database. (.Net SqlClient Data Provider)

Sad thing is, this is party true, the database is 10,911,616 KB in size (1,024 KB for logs), which, honestly, stumps me, since I didn't do anything with it after detaching and it worked up until then.
However I am very certain, there is lots of free space inside that database (something around 9GB)
So this is a three-part-question:

Can someone point me to the circumstances that could lead to this problem (database working on the very same server, then detached, not changed (at least by me), then not being able to re-attach?

Is there a way to free up space inside that .mdf file without actually attaching it?

Or could it be feasible to take the database I already attached to the new (full) SQL Server instance, shrink that in size and then attach it to SQL Server Express instance? (or would attaching to the full SQL Server instance change the format of said .mdf file to be unusable by SQL Server Express?)

Additional Server information:

New Database Server has version Microsoft SQL Server 2012 (SP1) -
11.0.3128.0 (X64)

Old Database Server has version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Database is running in compatibility level SQL Server 2008 (100)


texts:
I am attempting to use SUM() in my SET statement for my UPDATE.  When I try this, I get an error of

  Msg 157, Level 15, State 1, Line 234
  An aggregate may not appear in the set list of an UPDATE statement.

What should I alter in my CTE so that I can run this update?

;WITH CTE AS
(Select EC, CN, CID, EntryDate
        FROM OPENQUERY(192.55.108.214,'select
                              count(es.estimatenumber) As EC
                              ,cm.an CN
                              ,cm.am CID
                              ,es.entrydate EntryDate
                              from etalac es
                              INNER JOIN bacalac cm
                              ON es.CID = cm.am
                              GROUP BY cm.an, cm.am, es.entrydate'))
UPDATE d2016
SET TotalNumber = SUM(EC)
FROM CTE cte
INNER JOIN datafor16 d2016
ON cte.CID = ed2016.CID
WHERE CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016';


texts: How does a SQL Server instance alias work?

texts:
I'm trying to understand how a SQL Server alias works, but the documentation is lacking to say the least.
Most blogs/videos/documents I can find state that all you need to do is go to SQL Server Configuration Manager and add an "Alias" under SQL Native Client Configuration / SQL Native Client Configuration (32 Bit), which points to the same port as the instance you want to create an alias for. And that's it, done, nothing else required.
Now unless the SQL Server is in a domain, and allowed to register its own SPNs and only Kerberos is being used (and assuming that an alias gets its SPN automatically registered, which I can't find any documentation to either confirm or refute this statement)... I can't really see how this is enough!
Assuming that either the SQL box is not on the domain, or that SPNs are not registered, if I create an alias called SQLTEST for SQL1 on port 1433 and, from a different computer, try to access SQLTEST bearing in mind the lack of SPNs the first thing the client will do is try to resolve SQLTEST to an IP address - yet there's no DNS record for SQLTEST.
So how does this actually work? What are the true requirements to get aliases working?
How does creating an alias differ from just creating 'A' or 'CName' records in DNS without actually creating an alias in SQL itself?


texts: How to create a MongoDB index with isodate?

texts:
I've create and index for a query and its running is less then 1 second to retrieve millions of rows, but as soon as I add greater than ISODATE, it doesnt use indexes anymore.
the query is this one:
db.getCollection("Coll").find({"Field1.date" : {
                    $gte: ISODate("2019-11-27T00:00:00.000Z"),
                    $lte: ISODate("2019-11-28T00:00:00.000Z")
                }},{
    _id: 1,
    "Field2": 1,
    "Field3":1,
     Field4: 1
})

and i created an index like this:
//    db.Coll.createIndex(
//    {
//           _id: 1,
//    "Field2": 1,
//     Field4: 1,
//    "Field1.date":1
//    },
//    {background:true , name: "idx_name_date"})
//

but it seems this "field1.date" doesnt work with ISODate.


texts:
I have Database A & Database B. Both have a User table.

I need to write one stored procedure that will retrieve the data from the correct table depending on a @Parameter passed in.

The idea of the query (data needing returned) in the stored proc needed:

SELECT * FROM dbA.User || OR || SELECT * FROM dbB.User

I have found the following ways to accomplish this but I want to minimize duplicate code nor add performance slowness/overhead.

Possibilities on my list:

Dynamic SQL - harder to maintain, adds overhead in that my
understanding cannot define execution plans
Create a duplicate proc or If/Else inside proc - duplicate code
never good
Table value function - not sure of a way to do this using it as I
can't use dynamic SQL here or If/Else to tell which query to run.

At this point I am leaning towards Option #2, duplicate code, with the understanding the database setup needs to be better if we want to avoid duplicating code.

I am not saying this is a good database setup, but it is what I am faced with. I have no control over combining database A & B or changing the structure to only have one User table.

Is there any other way to take care of this issue? Or thoughts on my approach?


texts: Prevent gaps while using Identity/sequence object in sql server

texts:
I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is potential drawback of using Identity or sequence object; but is there any work around? I am open to using some other technique to prevent gaps.

Here is the table structure and sample code:

CREATE SEQUENCE Service_Ticket_Seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 100
 CYCLE;

 CREATE TABLE Meats
(ticket_seq INTEGER NOT NULL primary key default (NEXT VALUE FOR Service_Ticket_Seq) ,
 meat_type VARCHAR(15) NOT NULL)


texts: Which table should a foreign key live on

texts:
I'm learning about databases. In my very simple database, I have 2 tables.

WorkingHoursDetail

ID
NameOfWeek
Hours

and

WorkingHours

ID
Name

As you can see, they currently do not relate.

Now, I'm faced with the question of making them relate. I understand I do this by adding a foreign key, something like MyOtherTableID and linking this to the other tables ID.

In the example above, I could add a foreign key to EITHER

WorkingHoursDetail

ID
NameOfWeek
Hours
WorkingHoursId

with

WorkingHours

ID
Name

OR

WorkingHoursDetail

ID
NameOfWeek
Hours

with

WorkingHours

ID
Name
WorkingHoursDetailId

I've tried this, and it seems to work but by having this option frightens me. Is there a 'right' answer or will this change only affect the order in which data can be written to the database. If this is the case, then surely this will dictate the flow of the program using it (the users GUI).


texts: SQL Server 2016 Compatibility Level and Datetime 2

texts:
We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but we have encountered few issues there. We managed to find one particular one where a query converting datetime to datetime2 and it returns NULL when it should have returned a value, not sure if it is CE or something? Please see the example below:

CREATE TABLE TestCE
(
    Id           INT IDENTITY(1,1),
    CurrentDateTime  DATETIME
)
GO

INSERT dbo.TestCE(CURRENTDATETIME)
SELECT GETDATE()
UNION ALL SELECT '2018-04-11 08:44:42.643'
UNION ALL SELECT '2018-04-12 09:49:45.334'
GO

SELECT * FROM TestCE
--The resultset

1    2018-04-17 16:49:02.813
2    2018-04-11 08:44:42.643
3    2018-04-12 09:49:45.333

But when I run the following queries, don't get any result:

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')

I have also tried with QueryTraceOn option for backward CE but with no success:

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
  OPTION(QUERYTRACEON 9481);
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
  OPTION(QUERYTRACEON 9481);
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
  OPTION(QUERYTRACEON 9481);

Also set the following DB option but with no success either:

ALTER DATABASE SCOPED CONFIGURATION
  SET LEGACY_CARDINALITY_ESTIMATION = ON

As soon as I change the compatibility level back to 2008, queries start returning the data as expected.


texts: How define select, if bridge pair may have 3 players?

texts:
Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.

Table, members:

pair, player_id

1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 13

table players:

id name

1 Smith
2 Brown
3 Johnson
4 Wild
5 Hammer
6 Bolt
7 Pink
8 Bush
9 Novak
10 King
11 Holmes
12 Watson
13 Gold

Required result of select is:

Pair, Names
1, Smith - Brown
2, Johnson - Wild
3, Hammer - Bolt
4, Pink - Bush
5, Novak - King
6, Holmes - Watson - Gold


texts:
There is an error in the following function creating query:

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END

Any idea how to fix?
Can I avoid using ; in the query above? The development framework doesn't allow multiple queries in one call and identifies this one as multiple queries.

EDIT1:
The aim of the function to make each row unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.

EDIT2:
The error message is usual, that query failed. You have an error in your query.

EDIT3:
I use the function like this way:
SELECT
    _increase_num() as rownr,
    content
FROM mytable

WORKING SOLUTION:

CREATE FUNCTION _increase_num()
returns int
    return IF(@counter, @counter:=@counter+1, @counter:=1)


texts: stress-testing MySQL slave with master's queries

texts:
A MySQL host in a slave configuration is in the testing phase before being put into production as a primary server for a specific workload. To make an estimate about how it is going to perform, the idea was born to simply use the current productive master's queries and replay them on the slave. The imminent issues that arise are the following:

the slave already is replaying the master's binlogs, so only SELECT statements should be considered for execution
it needs to happen multi-threaded and in near-realtime (or at least in the same pacing as at the master) to get meaningful figures out of the whole process

Although I know about the general log, I do not see an easy way to meet both requirements. The first would require a more-or-less sophisticated parser to prevent execution of edgy things changing data like a SELECT [...] INTO. As for the second, I know about Percona Playback which is capable of parallel execution and query-pacing, but it only would take the slow query log format (at least for the pacing feature), which is even harder to parse. It also won't process in real time (e.g. the mysql-slow log piped from cat or nc).

As this does not sound too far-fetched as a requirement, I wonder how the other DBAs are managing this.


texts:
I'm trying to restore from a database backup to SQL Server 2008.  The backup came from a different server.  I'm executing the following query:

RESTORE DATABASE Database_Name
FROM DISK = 'C:\Path\To\Backup.file'
WITH REPLACE
GO

If the database does not exist, I'm getting the following error:

  Msg 3118, Level 16, State 1, Line 1
  The database "Database_Name" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The file is a full backup.

If the database does exist, I get the following error:

  Msg 3154, Level 16, State 4, Line 1
  The backup set holds a backup of a database other than the existing 'Database_Name'  database.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

From what I understand, WITH REPLACE is supposed to be a workaround for this.

I'm stumped.


texts:
I am new to report writing and am trying to suppress rows that have zero data.  I need to know how to write the conditional iif statement when I have multiple columns of data.  Example

account number  account description  fy 2012 fy 2013 fy 2014 fy 2015

some accounts have zero numbers across the years and I don't want that row to show.


texts: (Oracle)Mysql 5.6: Unable to find why the memory/RAM usage for this process is ~20GB

texts:
We have a observed a high memory usage by one of our MySQL(Oracle) instances running 5.6. We have verified the basic configurations and could find no clue about this. Below is the information that we captured during this analysis, please have a look at and let me hear back if you have anything to suggest.

Data:   Mostly Innodb
Type of load: 95% SELECT
Max connections: 100
Max used connections: 10
Current activity: Idle
Uptime: 30 days
Innodb buffer pool size: 5 GB(94% occupied by the time I post this)
open table cache: 30000
tables opened: 5
DB total size: 14 GB(data & indexes)

NOTE: One thing that we have observed suspective is most of the space is occupied by 4 - 5 tables on the server and are all partitioned. When we check file descriptors in /proc/$pid/fd, we could see pointers to these partions.
All these parttions are created with in last 30 days only. We are not sure why are these in memory and not released if they are in memory.

RAM being used is ~20 GB. Almost equalts to the size of innodb buffer +
total size of the database. Please let us hearback in case if you need any
other information.


texts:
I have a table

  | base_schedule_line_items | CREATE TABLE base_schedule_line_items (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    installment int(10) unsigned NOT NULL,
    on_date date NOT NULL,
    actual_date date DEFAULT NULL,
    payment_type int(11) NOT NULL,
    scheduled_principal_outstanding decimal(65,0) NOT NULL,
    scheduled_principal_due decimal(65,0) NOT NULL,
    scheduled_interest_outstanding decimal(65,0) NOT NULL,
    scheduled_interest_due decimal(65,0) NOT NULL,
    currency int(11) NOT NULL,
    updated_at datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
    created_at datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
    loan_base_schedule_id int(10) unsigned NOT NULL,
    lending_id int(10) unsigned NOT NULL,
    reschedule tinyint(1) DEFAULT '0',
    PRIMARY KEY (id),
    KEY index_base_schedule_line_items_loan_base_schedule   (loan_base_schedule_id),
    KEY index_bslt_spd (scheduled_principal_due),
    KEY index_bslt_lending (lending_id),
    KEY index_bslt_actualdate (actual_date),
    KEY index_bslt_spsila   (loan_base_schedule_id,scheduled_principal_due,scheduled_interest_due,actual_date),
    KEY index_bslt_ondate (on_date),
    KEY index_bslt_oa (on_date,actual_date),
    KEY index_bslt_ol (on_date,loan_base_schedule_id),
    KEY index_bslt_oli (on_date,lending_id)
  ) ENGINE=InnoDB AUTO_INCREMENT=30410126 DEFAULT CHARSET=utf8 |

Now this table has 30 million records in it, I need to add two more indexes to this
and its like it takes years to add it.

  alter table base_schedule_line_items add index index_bslt_sla     (scheduled_principal_due,actual_date,lending_id);
  alter table base_schedule_line_items add index    index_bslt_ssla(scheduled_principal_due,scheduled_interest_due,lending_id,actual_date);

I used the below mentioned query to find out size of table

  SELECT table_name AS "Tables",  round(((data_length + index_length) / 1024 /  1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema =   "my_database_name";

The result came out as

base_schedule_line_items       |   20111.00

I used this just to calculate data length and left out index length

  SELECT table_name AS "Tables",  round(((data_length) / 1024 /  1024), 2) "Size in MB"  FROM information_schema.TABLES  WHERE table_schema =   "my_database_name";

and result was

  base_schedule_line_items       |    9497.00

The indexes

  KEY index_bslt_actualdate (actual_date),
  KEY index_bslt_spsila   (loan_base_schedule_id,scheduled_principal_due,scheduled_interest_due,actual_date),
  KEY index_bslt_ondate (on_date),
  KEY index_bslt_oa (on_date,actual_date),
  KEY index_bslt_ol (on_date,loan_base_schedule_id),
  KEY index_bslt_oli (on_date,lending_id)

were added by me, but I am not fsure or what purpose other indices were added for. This is fairly big application . Now i need to add those two indices mentioned above as they help me extracting a report using a select statement and I find it very difficult to add them. Any help would be greatly appreciated