texts: Use Python to parallelize processing of rows, or is there a native way?

texts:
As mentioned in a recent question of mine, I'm new to Oracle:
Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine
I'm back, this time with a question about the best way to select some rows of a VIEW and then process each of these rows in parallel.
First, my setup:

I'm running SQL Developer on my MacBook.
My database is in a Linux VM installed using VirtualBox.
I'm using Python to query a VIEW that I've created in the database.
My VIEW is named 'myview'.

Next, here's what I've been doing in Python to process rows of 'myview':
The Python query of 'myview' goes something like this:
import cx_Oracle
conn = cx_Oracle.connect("JUSTIN", "oracle", "localhost:1521/orcl", encoding="UTF-8")
conn.version
cursor = conn.cursor()
cursor.execute("select * from myview order by case_id")

I want to run some function (or series of functions) on each row of the returned 'cursor' object. In Python, I can process each row at a time using something like this:
# Serial version

for row in cursor:
    row = cursor.fetchone()
    # Run desired functions on 'row' object:
    f1_out = function1(row) # <-- This function computes some values
    f2_out = function2(f1_out) # <-- This function writes a file
    f3_out = function3(f1_out) # <-- This function writes another file
    # ... etc.

# Parallel version (example taken from link below this code block):

import asyncio
import time

def background(f):
    def wrapped(*args, **kwargs):
        return asyncio.get_event_loop().run_in_executor(None, f, *args, **kwargs)
    return wrapped

@background
def process_a_row(row):
    # Run desired functions on 'row' object:
    f1_out = function1(row) # <-- This function computes some values
    f2_out = function2(f1_out) # <-- This function writes a file
    f3_out = function3(f1_out) # <-- This function writes another file
    # ... etc.

for row in cursor:
    process_a_row(row)

https://stackoverflow.com/questions/9786102/how-do-i-parallelize-a-simple-python-loop
Finally, the question...
I feel like there ought to be some way to do this server-side. For instance:

Are there PL/SQL functions that can be parallelized on all the nodes/cores that Oracle has access to?
Are these functions expressive enough to do things that Python can do, such as calling water property libraries, performing calculations with them, and reading and writing files to disk?

The reason I ask is because any user's client machine will have only 1 node and therefore much fewer cores across which to parallelize the row operations. But, perhaps, by running server-side, I can get a hold of multiple nodes of cores? In practice, does any one user have carte blanche access to multiple server nodes? Does Oracle natively span multiple nodes on a server, or is that a special DBA configuration option?
Thoughts? How would (do?) you do this?
Thanks!
Justin


texts:
I am trying to import a longish SQL script (3.5 millions of lines, 1,5 GB in size) containing mainly short INSERT INTO statements with data using sqlcmd -E -d <database> -S 127.0.0.1 -i <scriptfile> and it is nearly crawling to a halt (approximatly 150 lines per second) without inferring any noticeable load on the SQL server host (all CPU cores are nearly idle, Disk throughput is at around 200 KB/s).
As the source file is located on an NFS share, I first suspected this to be the culprit, but an identical script available locally runs at just the same speed.
The database is basically empty, the tables are created by the very same script, no triggers or other fancyness - just the raw, mainly numeric or varchar data.
What is SQLCMD waiting for? Is there any way to speed it up?
Edit:
we are limited in our means to alter the data in the SQL script files. The data is provided by a third party for an import procedure. I believe it was originally exported using Management Studio 2005 "Script table as..." feature.
Editing the file is tedious due to sheer size - any operation takes forever with common text editors, although prepending a "SET NOCOUNT ON" was done by copying two files together - and it brought a speedup of approximately 50%.
Due to the text encoding (Unicode-LE) it is impossible to edit with the common set of GNU textutils (which would otherwise work fine with large files) without converting - and I am reluctant to converting because of data fidelity issues which are likely to come with it.
So I am somewhat stuck with how to apply the suggestions on inserting the BEGIN TRAN/COMMIT TRAN blocks or converting the individual inserts into larger sets.


texts:
I was reading through the Postgres documentation on materialized views and in one example they use the following:

SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

I tried to run a similar query on my own database, but I get an error.

[42883] ERROR: operator does not exist: character varying <-> unknown

I (probably) don't need to use the operator, but I'm just curious what it does.

Notes:

I am running the same version as the docs (9.3)
I did try Googling the question, but Google just ignores the <-> even if you wrap it in quotes.


texts: Dropping old and creating a new clustered index

texts:
I am wondering what is the best approach for dropping and creating a new clustered index on a table within SQL Server 2008. My primary concern is the length of time it will take to perform this operation, as it is estimated to be greater than our maintenance window.

Background
The table is an 8 million row table, with 250 columns and 15 non-clustered indexes, 8 of which are larger covering indexes holding about 15 to 30 columns each, these are mainly used near month ends on reporting.

I have already reviewed unused and duplicate indexes and removed them.
The table has numerous websites, web services and polling processes selecting, inserting and updating records at a very high rate.
Things slowdown in the late hours but not fully.

The Index
The current clustered index is on 6 columns 4 of which are varchars and 3 of which are flags that are updated throughout the life of the record.
A record goes through about 18 states before it can be considered complete, meaning those flags are changing rapidly and constantly.
Everything about this clustered index is wrong, the combination is NOT unique, it’s on dynamic columns and it’s large.

I want to DROP this existing index and replace it with a new clustered index that is just the Id column, a non-identity int that is unique.
Note, this is not the Primary Key of the table, the PK has three columns.

In a lesser used environment dropping and creating the new index took just over 3 hours to do on a 2012 SQL Server.
I used the ONLINE mode which from my understanding does cause the process to run slower, but its required for any process that requires the table.

The questions -
Since the time to perform this action will be lengthy, I am trying to identify the proper steps to keep the length of time as short as possible.

Should I drop all the non-clustered indexes and create them after the process is done?
Should I copy the table perform the actions and then replace the existing table with the copy? My hesitation is we have replication and data backups etc. that need to be considered.
Should I run with ONLINE mode off, would that make a sizable difference in the length of time?
What other steps should I do to prepare for this? I am identifying records we can remove and any duplicate or unused Non clustered indexes.

We do have maintenance windows on our DB but they are usually shorter then this process takes and do not happen often so I am trying to determine the best course of action to take.

Edit: Also is there any way I can monitor this process to know the progress, beyond just the "Executing Query..." in the status bar.


texts: How to do an efficient case insensitive search

texts:
As an example, take a city table with 2 columns

city_ref (bigint)
city_name (varchar)

with indexes on both.

I want to do a case independent search by city_name.

I know I can use like but that's not very efficient.
One way would be to store the Uppercase variation of the city_name in another field.
But I can't help but think that there should be a way to use charset or collation to do this.

Can someone point me in the right direction please.


texts:
I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation.
I think about next tables

User
id
...

Conversation
id
user_id1
user_id2
cnt_new_msg
last_msg_id

Message
id
conversation_id
sender_id
type
is_read
created_at

For selecting data for list of conversation I can use next query:

SELECT *
FROM conversation c
LEFT JOIN message m ON m.id = c.last_msg_id l.last_ms
WHERE user_id1=[current_user_id] OR user_id2=[current_user_id]

When user send new message I will increment Conversation.cnt_new_msg and when user open conversation I will set Message.is_read to 1 and recalculate Conversation.cnt_new_msg again. Also to sync this value I can create cronjob.

But this approach has problems. I don't like user_id1=[current_user_id] OR user_id2=[current_user_id] for each query. My tables will be very denormalized but I think it will be faster.

Please advise best practices to design conversations tables.


texts: Amazon RDS Mysql with Binary Data Configuration

texts:
In Amazon RDS Mysql, when I run "\s", I see this (note the line: Binary data as: Hexadecimal)
mysql  Ver 8.0.22 for osx10.15 on x86_64 (Homebrew)

Connection id:      12230
Current database:
Current user:
SSL:            Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.31 Source distribution
Protocol version:   10
Connection:
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Binary data as:     Hexadecimal     <----------

What parameter sets this? Is this specific for Amazon RDS because I don't see this on my own MySQL server?


texts: grant all privileges of all tables in database to a user

texts:
I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that user. I wrote pl/sql command. but it gives error.

BEGIN
  2  FOR t IN (select * from dba_tables where owner not like '%SYS%' AND owner not like '%ADMIN%' AND iot_type IS NULL)
  3  LOOP
  4  EXECUTE IMMEDIATE 'GRANT ALL PRIVILEGES ON '|| t.owner ||'.' || t.table_name ||' TO GGSTEST';
  5  END LOOP;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4

What is the mistake? thanks.

I use dbms_print

GRANT ALL ON OUTLN.OL$ TO GGSTEST
GRANT ALL ON OUTLN.OL$HINTS TO GGSTEST
GRANT ALL ON OUTLN.OL$NODES TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_BASELINES TO GGSTEST
GRANT ALL ON APEX_040200.WWV_FLOW_LOG_HISTORY TO GGSTEST
GRANT ALL ON DBSNMP.BSLN_METRIC_DEFAULTS TO GGSTEST
GRANT ALL ON XDB.XDB$ROOT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$XDB_READY TO GGSTEST
GRANT ALL ON XDB.XDB$CDBPORTS TO GGSTEST
GRANT ALL ON XDB.XDB$IMPORT_TT_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$TTSET TO GGSTEST
GRANT ALL ON XDB.XDB$H_INDEX TO GGSTEST
GRANT ALL ON XDB.XDB$D_LINK TO GGSTEST
GRANT ALL ON XDB.XDB$COLUMN_INFO TO GGSTEST
GRANT ALL ON XDB.XDB$PATH_INDEX_PARAMS TO GGSTEST
GRANT ALL ON XDB.X$NM7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$QN7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.X$PT7UJB7VOLFE92KV0GUML7K0LVSF TO GGSTEST
GRANT ALL ON XDB.APP_USERS_AND_ROLES TO GGSTEST
GRANT ALL ON XDB.APP_ROLE_MEMBERSHIP TO GGSTEST
GRANT ALL ON XDB.XDB$DBFS_VIRTUAL_FOLDER TO GGSTEST
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 4


texts: Is this acceptable practice for a many to many?

texts:
As a newbie in database design, I have a question about many-to-many relationship practice.

This question is actually a follow up question of this question, but I found it too big to explain in a comment.

The design should be like this(picture below):

A person can have multiple phone numbers
A club can have multiple phone numbers
A phone number can only belong to either 1 club or either 1 person

As I understand it, this isn't exactly a many to many relationship but a many to one.
However, phone number can belong to 2 other tables.
So, should I add 2 columns to phone numbers then?
1 with a FK to person and 1 with a FK to club? Leaving one of them null when a phone number is entered?

Personally, I thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.

But,...
I find myself in the same situation when it comes to e-mail addresses.
So, my idea was to also link them to that same contact table.
Was this a good step, or should I have added a separate table for the relationship between club - email and person - email?

I hope my question is a bit clear considering its actually a follow up.

-edit-
My schema seems to be incorrect for what my intentions are.

a person/club can only have one contact
a contact can only belong to one person/club
a phone number can only belong to one contact
an email address can only belong to one contact
a contact can have multiple phone numbers
a contact can have multiple email addresses


texts: Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error

texts:
We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy applied

The login will work if the user is sysadmin (but the application won't)

Of course we have looked at Aaron Bertrand's post on 18456 errors, which states:

  States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin).

and also http://blog.sqlauthority.com/2015/08/19/sql-server-login-failed-for-user-reason-token-based-server-access-validation-failed-with-an-infrastructure-error/

So we have ensured that we have a profile on the servers by logging on with the service account.
We have run the application as administrator
We have confirmed connectivity to the DC
We have removed and re-created the login
We have confirmed there are no DENYs for the user or group

We have confirmed that the user and also the public role has connect permissions to the ConfigMgrEndpoint as well as SQL

What have we missed?
What else do we need to do to resolve this issue?


texts: Why does SQL Server 2012 use 6x as much space for same DB?

texts:
We have a legacy database in MS SQL Server 2005. It has the latest service pack, so the backup format is compatible with 2012. The database is around 9GB, nearly all of which is data (the transaction log has been backed up). The database backup is 9.1 GB. When we transfer the backup to the 2012 machine, and try to restore, it fails saying that there is insufficient disk space available. It says that 60 GB will be required to complete the restore.

Why is this? Does it have to make 6 temporary copies in order to migrate from 2005? Will it keep on using 60 GB after it is restored? Is there some way to do the restore without freeing up 60GB on the server?

Here is the output of sp_helpfile:

STARS_Data  1   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS.mdf  PRIMARY 9674880 KB  Unlimited   10% data only
STARS_Log   2   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS_1.LDF    NULL    299200 KB   Unlimited   10% log only

Note that the database only uses around 10GB on the 2005 server.

I backed up to a completely new file. There is only one backup in the file; it was not appended. There is no problem restoring to 2005: it doesn't use vast amounts of additional space.

The hard drive has 20GB free.


texts: I would like to know how to copy all the data from a database X1 into a database X2 on SQL Server please.


texts: SQL Server - Copy data from one database to another - identical tables, new identity values needed

texts:
Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together.

All tables that I need to merge are Identity based and foolishly the matching tables in each copy of the database were seeded at the same number.

Is there a way to copy the data from one database to another at the same time giving the copy data new identity values that also are reflected in the foreign key values of referenced tables?

i.e.

Account
- Id
- Email
- FirstName
- etc

AccountImage
- Id
- Account_id
- FileName
- etc

And so on..


texts: Predefined rows conflicting with user entered rows

texts:
Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them?

I could want to distribute an application with a table called Colors where I defined some colors that are, for example, used in the application's GUI.
I might use one column for a numerical primary key, one for the name and three other columns for R,G,B values. I would put an unique constraint on the name column and one for the three R,G,B columns.
The contents of this table are intended to be available to the user so that when he needs to specify a color he can choose one from the table.
However it makes sense to allow the user to add his own colors to the table.

Issues however could arise if the user wants to:

define a color whose R,G,B value are already used in a predefined color
use a name that is already in use by a predefined color
hide predefined colors he's not interested into

I need to have some predefined, read-only, rows on which the application can rely but at the same time I don't want to impose anything on the user while providing him with the opportunity to use some of the predefined rows if he chooses to.

I thought of separate tables or a single table with extra columns that specify if the row is predefined, hidden by the user and so on.
However I can see flaws in both approaches.


texts: MySQL query using left join on two inline views not returning rows without values in second view

texts:
For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second view if it was there. I'd like to be able to use 0 as the value for po.tot2 below if oli.sku is not in the po view.

SELECT
   oli.sku,
   oli.tot as oli_tot,
   po.tot2 as po_tot,
   oli.tot - po.tot2 as diff
FROM (
   SELECT
      sku,
      COALESCE(SUM(quantity),0) as tot
   FROM orderlineitems
   GROUP BY sku
) oli
LEFT JOIN (
    SELECT
       sku,
       COALESCE(SUM(amount),0) as tot2
    FROM poentries
    GROUP BY sku
) po
ON oli.sku = po.sku
WHERE oli.tot - po.tot2 > 0;


texts: Is there any way to find out who installed  SQL server on a Windows 2003 server?


texts: Why is Insert-Ignore so expensive in MySQL?

texts:
TL;DR:
Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax.

Why is this? I would assume that the index lookup cost would be the same for an insert and an "ignored" insert, given that MySQL does not know if the incoming data has repeated/conflicting data (and therefore needs to be ignored)...thus, indexing occurs in both the initial insert and the ignored insert runs.

Furthermore, I would assume that an "ignored" row should be CHEAPER in that it does not require any disk writes.

But this is most definitely NOT the case.

Long Version:
In this question, we use AWS's Aurora/MySQL and the LOAD DATA FROM S3 FILE syntax to remove any transfer or performance variables. We load a 4 megarow CSV file corresponding to the schema below, and load it in twice, both times with LOAD ... IGNORE.

Please note that the issue also occurred with standard INSERT ... IGNORE, but with use of batched row inserts. The use of LOAD ... IGNORE here is to steer the discussion towards the counter-intuitive nature of the measured results, and not "how to perform large number of ignored inserts". That is not the issue here, as a domain-specific method was worked out.

In the model being tested, there is a three-tier index: the first two being enumerable categorization columns with very low cardinality, and a third column that is essentially "actual" data. For ease of qualifying this question, I am just sticking to my current setup.

Assume the following trivial schema:

our_table:

id: basic auto-increment bigint, primary key
constkey1: varchar(50) -- this is a constant in the insert
constkey2: varchar(50) -- this is a constant in the insert
datakey:   varchar(50) -- this is pulled in from the CSV file
datafield: varchar(50) -- this is pulled in from the CSV file
consttime: datetime    -- this is a constant in the insert

Unique Index: (constkey1, constkey2, datakey)

And the following query, run twice:

LOAD DATA FROM S3 FILE  's3://...'
    IGNORE
    INTO TABLE our_table
    COLUMNS TERMINATED BY ','
    (datakey, datafield)
    SET constkey1 = 'some string',
        constkey2 = 'some other string',
        consttime = CURRENT_TIMESTAMP  -- so that I can verify changes

Also assume that our_table already contains several million rows, NONE of which have have NEITHER the same constkey1 NOR constkey2 as that given in the query.

The first time I run the query above, the 4 megarows take 3 to 5 minutes to insert (presumably based on the vagrancies of the current database environment at the time of the run), and all 4 megarows are properly inserted. This is an acceptable baseline.
The SECOND time I run the query, the 4 megarows take an insane amount of time to (not) insert, and as expected, all 4 megarows are properly NOT updated. The time is long enough to not even be worth qualifying here (one run nearly an hour).

This test was run a number of times, always yielding the same result.

UPDATE:
While modifying this question, the following question was suggested by S.E.

mysql insert into indexed table taking long time after few million records

While I do not think it completely generic here, perhaps it has something to do with memory size of the index range? In the example here, the "sub index" size of ('some string', 'some other string', * ) was ZERO in run one, while full of 4 megaentries in run 2. Perhaps That is an issue?

Please also note that the eventual solution was of the form given in the question below. This is immaterial to the question at hand, as I wish to know why the original solution did not work. This is merely given for those curious.

Can I identify duplicates in a large MySQL insert?


texts: Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

texts:
I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering either not adding this field to the covering index, or indexing only the first 800 characters.

When retrieving rows from this table, will MySQL know that the matched rows have values that fit inside the indexed portion? If this is so, then I'll just index the first 800 characters and for most queries MySQL will not have to hit the table.

If MySQL does not know that the entire field was indexed for all matched rows, and thus must hit the table for this field anyway, am I doing any good with having the other SELECTed fields in the covering index?

These are my options:

# baz is a text field
SELECT foo, bar, baz FROM users WHERE id=42;

# If MySQL will know that the matched rows have values that
# fit inside the indexed portion
ALTER TABLE users ADD INDEX (id, foo, bar, baz(800));

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing the
# other SELECT fields helps
ALTER TABLE users ADD INDEX (id, foo, bar);

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing
# the other SELECT fields does not help
ALTER TABLE users ADD INDEX (id);


texts: how to restrict usage of datafile in Oracle

texts:
We have several datafiles with a big amount of a free space.
How to shrink those files on production system. I tried to move all objects from this files into another tablespace, but RDBMS is still putting something there near MAX block number that makes "ALTER DATABASE DATAFILE RESIZE" impossible.
is there any option to block file to get create segments to make it possible to have resize as an empty file?


texts: Use ANY/ALL to retrieve customer who placed the highest number of orders

texts:
I have this tables, and i would like to retrieve the customer who placed the highest number of orders.

i wrote this query:

select * from customers where cust_id=(
select cust_id from orders
group by cust_id
having count(*)>=all(select count(cust_id)
                     from orders
                     group by cust_id))

I know that maybe there is a better way to do it, but I was surprised to find that 'ALL' works with '> ='

In my understanding, 'ALL' checks whether the current row is greater or less than all rows in the sub-query, but I never thought it was possible to use it with '='.

If i use it with '=' or '>'  the query doesn't return me any rows as i was expecting.

But if i use them together '>=' the query gives me the correct results.

Isn't it strange?

Anyway, at the end i wrote this query:

SELECT *
FROM Orders, customers
WHERE orders.cust_id=customers.cust_id
and orders.cust_id IN
            (SELECT TOP (1) o.cust_id
            FROM Orders AS O
            GROUP BY O.cust_id
            ORDER BY COUNT(*) DESC);

Do you have a better or more elegant solution?

And isn't it strange the use of '>=' in 'ALL' clause?

Thank you.


texts: Referential Integrity - Indirect Foreign Key In "Depth"

texts:
Question

What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship trees.

Details Since That Question Doesn't Likely Make Sense

I am attempting to build a database for keeping track of the execution status of automated processes running in our environment.

In general we have a "Job" which triggers one or more "Executables" and those "Executables" can run tasks for one or more customers.  We will then have 2 logging tables, one that tracks when a "Job" was started, and another that will log the Success vs Failure status of each "ExecutableCustomer" instance.

A Planned Simplified Schema is below:

When we right the record to the JobExecutableCustomerExecutionLog, I would like to ensure that the Job.ID value associated with JobExecutionLog.ID value matches the Job.ID value associated with JobExecutableCustomer.ID.

Normally I would handle this with a Foreign Key but since Job.ID is not stored on JobExecutableCustomer, JobExecutableCustomerExecutionLog nor JobExecutionLog.  The Relationship is indirect.

Example:

I have 2 jobs, "Send Email" and "Send Text Message".  "Send Email" initiates a single executable which belongs to 1 Customer. "Send Text Message" has 2 executables (both of which execute for the same customer). I want to make sure that when the record is written to JobExecutableCustomerExecutionLog for "Send Email" the Job.ID associated with JobExecutableCustomerExecutionLog.JobExecutableCustomerID and JobExecutableCustomerExecutionLog.JobExecutionLogID (after walking the relationships up) actually belong to the Job.ID for "Send Email" not "Send Text Message".

As I see it I have 2 options:

Push the value from Job.ID into all the child tables, and make it part of the Foreign Key
Have another process (Trigger or Indexed View) ensure the relationships for me

I personally don't like the idea of pushing the Job.ID value on all the other child tables, so I am leaning towards using a Trigger or something else to handle it.  I didn't know if those were my only two options or if I have the ability to configure a "normal" Foreign Key to traverse the relationships all the way up.  In some kind of Cascade or something else.


texts: Share centralized queries for multiple databases

texts:
OK, here's a challenge for you guys.

I am tasked with setting up reporting for two companies being run as a single company.  For reasons I won't get into, they are implicitly split but (for our purposes) are run identically.  They each have two software implementations for which I have access to the databases.

What is being done currently is we have a suite of queries that output to reports in various formats, and the logic is replicated between the two companies, making for 4 sets of queries.

This graphic represents the DB summary, and for each of them there are reports.  We are using MS SQL Server.

We may add additional software in the future, turning 4 suites of queries into 6, 8, etc.  I need to ensure that the query logic has no variation between the two companies in order to minimize the risk of discrepancy and for ease of development.

So here's the question:

Is it possible to share queries somehow between companies without using Dynamic SQL?


texts:
This is my query

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1

It is working properly.

Now I want to fetch the table name for the respective productName in future.

So how can I fetch it?

I tried AS.. like this:

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;

but didn't get output.

How shall I correct the query and also Improve the performance of query?


texts:
Whenever we apply a patch (recently with CU5), end users need are prompted for administrator credentials to print. In googling, I see many different suggestions, but nothing seems to work.
I have tried adding the SSRS server to the trusted sites list. Also, I have checked the active X permissions and they seem to be set correctly under Security Settings.
Does anyone know the necessary configuration to avoid this prompt with future SQL Server updates on our SSRS server?
I've followed the Microsoft directions for RSClientPrint - in looking at the users Internet Security settings, they appear to be in line with those recommendations.


texts:
When I type lsnrctl from my command prompt the below error message is displayed:

oracle@Gaurav:~$ lsnrctl
bash: /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl:
cannot execute binary file: Exec format error

Can anyone please provide me lsnrctl for oracle 12c, so that I could replace it?


texts: Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

texts:
I'm developing an inventory system for RDBMS deployments. I'm starting with SQL Server, and later will be pulling in info for Oracle & other RDBMS deployments. When you query [dm_os_windows_info] there is a numeric value in the windows_sku column that is of use to me.... if I can reconcile it.

 SELECT [windows_sku] FROM [master].[sys].[dm_os_windows_info]

I can't seem to find a simple reference on reconciling this SKU value to the product. If you look at the dm_os_windows_info reference, it says to see the GetProductInfo function. On that page you find a hex value. Looks like if you convert windows_sku to hex and take the right two values it works. If anyone knows of a simpler convert table that would be great. If not I'll probably have to make one. If I have to manually put the data in a table to reference it, I may as well convert it from hex while I do it instead of doing it on the fly.

Also it looks like this view was added in SQL Server 2008 R2, yet you can see version info in server properties for earlier version instances. Where is this stored?

Note I'm trying not to have to jump out to Powershell if possible. Also trying to avoid a dependency on OLE automation being enabled.

Thanks for any help! :)


texts: MariaDB are bitwise operators defined on strings?


texts:
I want to dump a database in a remote server www.myweb.io with ssl. The mongo version is 2.6.12. It seems that I have to use mongoimport. I am looking at /etc/nginx/sites-enabled/myweb.io:

server {
    listen 443 ssl;

    server_name myweb.io www.myweb.io;

    ssl_certificate /etc/letsencrypt/live/myweb.io/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/myweb.io/privkey.pem;

    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    ssl_prefer_server_ciphers on;
    ssl_dhparam /etc/ssl/certs/dhparam.pem;
    ssl_ciphers 'ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:... ...

But I just realized that there is no /etc/letsencrypt/live/myweb.io/fullchain.pem and /etc/letsencrypt/live/myweb.io/privkey.pem in my server. Does anyone know why the website still works?

Then in this case, where should i find --sslCAFile or --sslPEMKeyFile to make mongoimport work?


texts: generate array of unique uuid in postgreSQL

texts:
I would like to insert array of unique uuid values in table with uuid column as primary key. Table structure is

CREATE TABLE Organisation (uuid id PRIMARY KEY DEFAULT uuid_generate_v4());

e.g. I have 2 organisations, so I am trying to implement it with query looks like

INSERT INTO Organisation (uuid) VALUES (unnest(array_fill(uuid_generate_v4()::uuid, ARRAY[2]))) RETURNING uuid as uuid_org;

It fails with

ERROR: duplicate key value violates unique constraint "organisation_pkey"

because query fairly tries to insert two duplicated not-unique values, e.g.

{711ec909-fc2c-45aa-8912-39207788768e,711ec909-fc2c-45aa-8912-39207788768e}

My trick works when RETURNING ids or uuids while inserting in tables with not the only column - I pass the uuid column and query automatically generate unique uuids that I can aggregate (see below for table TimeSlice).

Generally, my idea is to insert simultaneously in several tables using WITH:

WITH ins_ts as (INSERT INTO TimeSlice (validTimeBegin) VALUES
             (unnest(array_fill('2020-01-02'::datetype, ARRAY[2])))
             RETURNING id as id_ts),
     ins_org AS (INSERT INTO Organisation (uuid) ... ---my fail query ),
INSERT INTO OrganisationTimeSlice (idTimeSlice, uuid, name)
            VALUES (unnest(array(select array_agg(id_ts) from ins_ts)),
    (unnest(array(select array_agg(uuid_org) from ins_org))),
                     unnest(array['name1', 'name2']));

Where I am wrong?


texts: We have a 3 node percona xtradb cluster and two slaves attached to it. One of the slaves we want to use it for taking backups. We have GTID based replication setup between the slave and the Xtradb cluster. My question is if we are adding a new node to the xtradb cluster can we use the backups from the slave and restore on the new node. Also would we be able to avoid SST and do IST instead. I am not sure how that part works since the backups are from a slave. Any help will be appreciated.


texts: oracle 11g: increasing buffer cache with AMM

texts: On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4G will halve the physical reads, and we have 4G to give. How must we do this? By increasing memory_target and letting Oracle grab more memory for the buffer cache? Or by explicitly setting a value for db_cache_size (which now is zero)?


texts:
Need to understand below behavior of tempdb:-

Due to some poor running queries which caused Tempdb bloating, we had shrink and tried to adjust the sizes of each tempdb file:

Before issue:

no of TempDB data files 8

Size of each TempDB data file 100 GB

During issue:

  no of TempDB data files 8
  Size of each TempDB data file 200 GB

Due to above we had entire disk of 1.6TB filled out, hence we shrinked and adjusted the file to 120 GB each thus allocating 960 GB to temp drive from that 1.6 TB and disabling autogrowth

Later after couple of days we see below

  no of TempDB data files 8
  Size of each TempDB data file 50 GB

I am not able to get above why tempdb on its own went to 50 GB each file and how even though we though of fixing its initial size to 120 GB each ?

Adding some info: - NO restart happened or anyone manually reducing the file size further to 50 GB. Autogrowth was also disabled since the last shrink change.


texts: PostgreSQL: How do I stop PostgreSQL from generating logs for PL/pgSQL functions?

texts:
I've defined some PL/pgSQL functions which get invoked from the client (e.g. psql, but could be JDBC/Hibernate as well), which is connected using a non super user role. e.g. SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd'); I've also set log_statement = 'none' in the postgresql.conf of my server. That stops logging of all SQL statements. However, the signatures and invocations of the PL/pgSQL functions are still being logged, leaking sensitive data into the log files.
How do I prevent this?
On further investigation, it looks like the RAISE LOG statement in my function is causing this additional information to be printed. So, although it is just a log message, it seems to be getting treated as an error. Please correct me if I'm mistaken, but from reading the manual, there does not seem to be a way to suppress these additional messages while only printing the message from the RAISE LOG statement alone. An option like log_log_verbosity would have been useful.
2021-02-27 17:42:58.483 IST [13008] myrole@test_db CONTEXT:  PL/pgSQL function chk_pwd(email_t,character varying) line 10 at RAISE
2021-02-27 17:42:58.483 IST [13008] myrole@test_db STATEMENT:  SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd');

Finally, as per postgresql.conf, the definition of log_min_error_statement considers log at a higher error level than error, which is quite unintutive.I ended up resolving the situation by using RAISE NOTICE instead of RAISE LOG. However, the verbosity of NOTICE is controlled by log_error_verbosity. So, it continues to show the CONTEXT, though thankfully not the STATEMENT, thus preventing the leakage of sensitive values from the function parameters.
Strictly speaking, I would not expect log_error_verbosity to be applicable to NOTICE, since it is not an error. Verbosity for NOTICE should be controlled by log_notice_verbosity (or log_non_error_verbosity), but there is no such setting available.


texts: What are the available options to backup DB2 Express-C?

texts:
I want to use DB2 Express-C edition with my application. I have never used DB2 but now want to use the Express Edition in a production environment to learn more about it.

I want to know what are the various backup options available in the Express Edition. In case of a sudden Windows DB2 service failure, how to recover data files?

Is there a way to just copy data files from one PC and attach on another instance of DB2 running on another PC?


texts: Can changing database owner affect permissions or role memberships?

texts:
A couple years ago we ran into a situation where we were changing the database owner from a DBA's login to sa, and immediately thereafter we received a complaint that the service login (which was NOT the DBA's login) no longer had access to the database.  On checking, the service login had indeed lost it's role memberships.

We only had 3 DBAs at the time, and nobody else had access to make a role membership change, so I'm pretty sure that someone did not specifically remove the service login from the roles.  However, I have not seen any warnings on articles about changing owner about something like this occurring, so I've not been able to explain it, and we couldn't reproduce the issue.

So, for example, the database owner was changed from DOMAIN\jdoe, and Windows login "DOMAIN\appuser" mysteriously lost it's built-in role membership.

Is there a situation where changing the owner of the database would cause permission or role changes?

Note that this question pertains to changing the actual database owner, NOT removing a login from the db_owner role.


texts: Revisit SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

texts:
I am having the same issue as SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store even with SSIS built in VS 2019 with the ODBC Driver 17 for SQL Server. I am using .netProviders/odbc data driver and getting the following error:

Error: 0xC020844B at Data Flow Task, ADO NET Destination
[2]: An exception has occurred during data insertion, the message returned from the provider is:
ERROR [CE269] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
Error 12038 sending request to https://[****].vault.azure.net:443

ERROR [CE263] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
****Error verifying signature of ECEK.****

ERROR [CE202] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
The keystore provider AZURE_KEY_VAULT failed to decrypt the ECEK https://[****].vault.azure.net:443/keys/CMKAuto2/[****...] with RSA_OAEP.

Not sure what I am doing wrong here. My DB is Azure SQL DB. I can view the decrypted data from SSMS and can insert a row as well. But my SSIS data flow is not working.

Can anybody please help me out?


texts: Point-In-time-recovery in Greenplum Database

texts: We have recently setup greenplum. Now the major concern is to setup strategy for PITR. Postgres provides PITR capability but I am a little confused as how will it work in greenplum as each segment will have it's own log directory and config file


texts: Unable to add a new connection to server in PostgreSQL using pgAdmin

texts:
I am new to PostgreSQL and trying to configure it. After installing the PostgreSQL. I am trying to add a new connection to server using pgAdmin. I know there is a by default created on port 5432 but I change this port to 5433. And trying to configure all my database on this port. And through terminal this is working.

But when I am trying to add a new connection to a server it throws

09:56:56: Error: Error connecting to the server: FATAL:  password authentication failed for user "tars"
FATAL:  password authentication failed for user "tars"

I have already followed this thread here. And also tried configuring pg_hba.conf. Changed this line

local   all             all                                     peer

to

local   all             all                                     md5

But couldn't add a new connection.
This is the screenshot, the way I am trying to configure

Can someone help me to create a new server ? And also help me to understand this little bit.


texts: Set Warnings (Alerts & Thresholds) in MSSQL Replication Monitor using TSQL

texts:
How can I configure the settings on this screen in Replication Monitor using TSQL?

For reference, we're on SQL 2014.


texts: Create row level trigger for Partitioned table

texts:
I have created a partitioned table (base), and an audit table for it(base_audit) and then created child tables (c1,c2,c3,..cn). To populate entries in base_audit, I have created a function and a row level trigger as below.

--Initial tables and partitions
create table base(id bigint, val text) partition by range (id);
create table base_audit(id bigint, val text, act char);
create table c1 partition of base for values from (1) to (3);
create table c2 partition of base for values from (4) to (6);
create table c3 partition of base for values from (7) to (10);

--Trigger function
CREATE OR REPLACE FUNCTION base_function() RETURNS trigger AS $$
BEGIN
if (tg_op = 'UPDATE') then
    if (old.id = new.id) then
        insert into base_audit values (old.id, oid.val, 'u');
        return new;
    else
        insert into base_audit values (old.id, oid.val, 'i');
        insert into base_audit values (new.id, new.val, 'a');
        return new;
    end if;
    elseif (tg_op = 'DELETE') then
        insert into base_audit values (old.id, oid.val, 'd');
        return old;
    end if;
    return new;
END; $$ LANGUAGE plpgsql;

--Trigger
CREATE TRIGGER base_trigger BEFORE INSERT OR DELETE OR UPDATE ON base FOR EACH row EXECUTE PROCEDURE base_function();

When I try to run the above create trigger sql, I am getting the below exception

ERROR:  "base" is a partitioned table
DETAIL:  Partitioned tables cannot have ROW triggers.
SQL state: 42809

I couldn't get what I am missing here. Is it something to do with Aurora Postgresql?

(Using Aurora Postgresql 10.7)


texts: Find rows where integer sequence contains a given subsequence

texts:
Problem

Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL.

I have a table representing sequences of integers. The definition is:

CREATE TABLE sequences
(
  id serial NOT NULL,
  title character varying(255) NOT NULL,
  date date NOT NULL,
  sequence integer[] NOT NULL,
  CONSTRAINT "PRIM_KEY_SEQUENCES" PRIMARY KEY (id)
);

My goal is to find rows using a given subsequence. That is to say, the rows where the sequence field is a sequence that contains the given subsequence (in my case, the sequence is ordered).

Example

Suppose the table contains following data:

+----+-------+------------+-------------------------------+
| id | title |    date    |           sequence            |
+----+-------+------------+-------------------------------+
|  1 | BG703 | 2004-12-24 | {1,3,17,25,377,424,242,1234}  |
|  2 | BG256 | 2005-05-11 | {5,7,12,742,225,547,2142,223} |
|  3 | BD404 | 2004-10-13 | {3,4,12,5698,526}             |
|  4 | BK956 | 2004-08-17 | {12,4,3,17,25,377,456,25}     |
+----+-------+------------+-------------------------------+

So if the given subsequence is {12, 742, 225, 547}, I want to find the row 2.

Similarly, if the given subsequence is {3, 17, 25, 377}, I want to find the row 1 and the row 4.

Finally, if the given subsequence is {12, 4, 3, 25, 377}, then there are no rows returned.

Investigations

First, I am not completely certain that represent sequences with an array data type is wise. Although this seems appropriate to the situation; I fear it makes more complicated handling. Perhaps it is better to represent the sequences differently, using a model of relations with another table.

In the same way, I think about expand the sequences using the unnest array function and then add my search criteria. Nevertheless, the number of terms in the sequence being variable I do not see how to do that.

I know it is also possible to cut my sequence in subsequence using the subarray function of intarray module but I do not see how it benefit me for my search.

Constraints

Even if at the moment my model is still being developed, the table is intended to be composed of many sequences, between 50,000 and 300,000 rows. So I have a strong performance constraint.

In my example I used relatively small integers. In practice, it is possible that these integers become much larger, up to overflow bigint. In such a situation, I think the best is to store numbers as strings (since it is not necessary to perform these sequences of mathematical operations). However, opting for this solution, this makes it impossible to use the intarray module, mentioned above.


texts: Run stored procedures from specific package/with specific tag at lower priority

texts:
I have this requirement to run "Maintainance Jobs" for a web application. These jobs will just call a stored procedure from a set of specific packages.

Running these jobs can consume quite a bit of CPU time due to the sheer amount of work they need to do (most of it is read, to determine work to do). Due to that, they compete with actual consumer traffic.

Now, can I somehow tag or otherwise indicate that these jobs have a lower priority than queries executed by the customer facing frontend application?

I've seen Oracle Resource Manager, which would allow me to specify a "Resource Plan" based on a module/action name. But it's not quite clear from the documentation if I have to set this explicitly in the SP (which I'm trying to avoid) or if Oracle would do this for me. I'm also open for suggestions not using resource plans.


texts:
I have two MySQL Database Servers i.e.
One is Active Server --> Master and second is Passive Server --> Slave.

And as we know we have 3-4 MySQL HR,DR Solutions / Clustering options.For example

01 : MySQL NDB Cluster

02 : MySQL InnoDB Cluster

03 : MySQL Cluster Carrier Grade

My First Question is i want to deploy One Active & One Passive MySQL Server so which Cluster Option / HA,DR Option will be fit for above mentioned scenario ?

My Second Question is suppose i have deploy One Active & One Passive MySQL Server in any above mentioned Cluster Option / HA,DR Option. So what about licensing ? I have to buy license of MySQL Servers/ Cluster ?

Regards,

Mubashar Iftikhar


texts: Is it possible to have 2 backup plans for a single database?

texts:
Is it possible to have two backup plans ?

allow sysadmins to have their own SQL Server 2008 set of backups for a database
allow DBAs to have their own separate backup plan

We have had issues with the sysadmin backups not working and would like to have a separate set for recovery.


texts: Postgres role security for multi-tenant database

texts:
I have a multi-tenant application where the Postgres DB schemas are set up as follows:

_admin (tables that are invisible in the UI to standard users but need to be read-accessible, common across all tenants)
_master (table defs only, no keys or other constraints)

customer (columns...) CHECK false NO INHERIT
etc (columns...) CHECK false NO INHERIT

acme (primary tenant-specific schema for tenant "acme")

customer INHERITS _master.customer

acme_oms (order mgmt schema for tenant "acme")

etc INHERITS _master.etc

acme_xx (where xx is another schema for tenant "acme")
widgets_inc (primary tenant-specific)
widgets_inc_oms (etc)

The _master schema contains all table definitions that will be inherited in the tenant-specific schemas. This works well. I can place CHECK false NO INHERIT on all these table definitions to ensure that nothing is ever inserted into them.

I'd like to set up the roles this way:

db_admin superuser (has access to everything including schema
modification and replication)
db_acme (has transparent read access to _admin,
read-write access to everything in all schemas starting with "acme", but no schema modification privileges, no access at all to other tenant schemas)
db_widgets_inc (etc...)

Two questions:

What would the CREATE ROLE code look like for the tenant-specific users?
Would restricting read access to the _master tables still allow the tenant-specific users to access the data in their own schema tables?


texts: Better Way of Releasing Massive Unused storage from Data Files to Server

texts:
After we did massive truncate of data, there is a huge unused space in multiple databases data files, and I wonder what would be the best way of releasing it back. I know most of the experts are against it, I am not talking about single digit GB space, its more 56% unused space out of 800GB data files, and there are many more files in the server.

SQL Version - 2014 Standard ( this cannot be changed, and no more storage can be added)

I know the below options could work, but also potentially cause fragmentation.

DBCC SHRINK (causes massive fragmentation and uses back some space while update status).
Moving the Primary File Group table to Secondary File Group.
Adhoc shrink in Chunks (this also causes fragmentation).

Any suggestions above this and new ideas are greatly appreciated.


texts:
I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for sql server's mdf/ldf files.

I read that there are dbf's/ctl's instead but I'm not sure which one is what I need. Also there aren't any files named after the database user that owns the database (after migration, it's the user that has the database) which leaves me puzzled.

I need to know which files are needed so I can copy the oracle database to another remote oracle server easily.


texts: Select query taking too long MYSQL 5.6.17

texts:
I have the below select and can't understand why it's taking about an hour to give me the result.

SELECT
(CASE
    WHEN a.ACODE='000A' THEN c.datfinprel
    WHEN a.ACODE='000T' THEN c.datiniprel
    WHEN a.ACODE='000S' && a.ACODETP=1 THEN c.datfinprel
    WHEN a.ACODE='000S' && a.ACODETP=2 THEN c.datiniprel
END) AS DATA_PREL,

A.`date_doc`, DATE(A.`created`) AS created, A.`stagione`, A.`fabbisogno`, A.`modello`, A.`commessa`,A.`code`, A.`title`, A.`color`, A.`measure`, A.`alt_pz`, A.`dist_base`, A.`qnt`,B.price, A.`cause`
FROM
    wb_mod_deposit_out A, wb_mod_deposit B, from_generale_java C
WHERE
    A.`stagione` IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')
AND B.ID=A.`deposit_id`
AND a.`code`=B.`code`
AND a.`color`=B.`color`
AND a.`stagione`=c.`stagione`
AND a.`modello`=c.`modello`
AND TRIM(LEADING '0' FROM a.`commessa`) = TRIM(LEADING '0' FROM c.`commessa`)
AND a.`type`=1;

The extended explain:

id  select_type   table               type    possible_keys                                   key           key_len  ref                    rows  filtered  Extra
 1  SIMPLE        C                   index   stagione,generale                               generale      123      (NULL)                 3729    100.00  Using where; Using index
 1  SIMPLE        A                   ref     stagione,test_OUT,CODE+COLOR,modello+comm+stag  stagione      153      csm_db.C.stagione       261    100.00  Using index condition; Using where
 1  SIMPLE        <subquery2>         eq_ref  <auto_key>                                      <auto_key>    153      csm_db.A.stagione         1    100.00  (NULL)
 1  SIMPLE        B                   eq_ref  PRIMARY,test_depo                               PRIMARY       4        csm_db.A.deposit_id       1    100.00  Using where
 2  MATERIALIZED  wb_mod_deposit_out  range   stagione,modello+comm+stag,date_created         date_created  6        (NULL)                15245    100.00  Using index condition; Using MRR


texts:
This is a question more so on the table definition vs actual programming.

I have 6 sets of potentially 255 unique fields.
I have a potential of getting 40 million records per year of these.

For the most part, I'm working with old and new data. The older the data, the more empty the data is. The newer the data, the more fields are filled.

I'm using SQL Server 2014, is it more efficient to make a table with 255 rows, or, create a mapping table to single data byte rows?

I guess the question boils down to: Is large amount of rows (something like 200x as large) better than a sparsed table?

Previous suggestions at (incorrectly posted at SO) mentioned normalizing. I have attempted to do that using the mapping table of string data. The problem lies mostly in the uniqueness of the string fields which is, unfortunately, mostly unique. Creating mapping per field would increase the cost more than decrease applicable situation. If I were to do that anyways, I feel that a sparse table would be better across the board.

Unique: This is data gathered about a situation; For example if we take a sample of the air right now, there's particulates in it. The data contains the values of the particulates in the air. Each sample is uniquely different, but, there will be 255 samples. (This was an example; In my case, I am gathering data that represents itself in strings; Though it's binary encoded so I could convert to binary.) Unfortunately each sensor I use, doesn't have to report all 255 samples. They can report whichever they please. So, I could use sensor A, which reports 1-20, sensor B which reports 3,9, 40-90.

What I plan to do with this data: There are tables that describe the test executed and the sensors used. These tables, are going to be my parameters to my query. I plan on querying: If sensor a, with parameters X = 2, Y = 2, Z = 2, exists, present me the data in columns 3-5, 25-190, and 252. This is a simple data returned query. That's going to be mostly what I do.

Once again, thanks for your time - replies, and posts.

Thanks BlD


texts:
There is a table person (which has among autoincremented primary key field id such fields as firtsname and lastname).

There is a table employee whose primary (but not autoincremented) personid is a foreign key for person.id. (Thus every employee is a person.) employee has several additional fields, such as the date of hiring the employee.

Whether a person is an employee can be checked knowing his ID by checking whether there is a employee row with this personid.

Now I need to introduce a few more kinds of persons:

children
kins of children

A way to do this is to add to person table an ENUM field: ENUM('other','child','kin').

But I wonder that it could be done in different ways for employees versus childs and kins.

Should I create special tables for children and kins, just like a special table for employees, even despite for now these tables may have only one field, the primary key personid?

Well, it may happen that in the future I may add more fields to these tables, but I'm not sure whether it even happens.

Also having separate tables for childs and kins would allow to manage foreign keys better, such as the obvious foreign keys for the table which relates childs and kinds by their degree of kinship.


texts: Session in a limbo state - Killed/Rollback

texts:
I have a SQLjob that calls xp_cmdshell that is used to dump a txt file to an ftp site via a script.

At times it fails and the job gets hung up in an executable state. Generally I would kill the session and it gets cleared.

However, I have two sessions that are hung in a limbo state.

 sp_WHO2 53 -- running from 7/8/2018

SP_WHO2 98 -- running from 6/16/2018

The job status says its Runnable but the Command is KILLED/ROLLBACK.

Checking SPID 53

Kill 53 with status only

Output

SPID 53: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Checking SPID 98

KILL 98 with status only

Output

SPID 98: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

How do I go about clearing these sessions?

I am having performance issues with the SQL Server, which led me to these sessions.
I did some research and points me to believe that it will increase the log files and cause performance issues. I am unaware if the current issue is because of these sessions but they are active expensive queries. So I thought of starting from here. I am developer here and I am aware that activity monitor is obsolete in SQL SERVER 2014 But i did check activity monitor and below are the images.

Can you suggest how I can go about it?

I did read a blog about such a scenario by Brent Ozar
- Rollback: What happens when you KILL a session?.

Learnt the hard way, looking into fixing the root cause.

AK


texts:
I am seeking advice before releasing my mongodb server into production from deployment mode.

Just to point out the server is working fine on development mode.

Scenario: Let's say I have two server Ubuntu 16.04 instances, where instance A is the (application instance), and instance B is the (mongo db 3.2 database).

For this instance B I don't think that I need scale/sharding/replicas, the idea is to let mongo occupy this instance all by itself to store secured users' credentials and some api data. (if you object, I am listing :) )

How they communicate: Instance B only talks to Instance A through port 27077 since it's not recommended to use the default port 27017. Moreover in the config file of mongo I bind the A's static ip.

Just to point out only three ports are allows in that instance B, ssh and 2 mongo db port 27077 and 28017

  First Question: In order for this communication to be secure, would it
  be a better idea to use HTTPS port 443 instead of that basic TCP port 27077
  , then disable the HTTP interface in the mongo.conf file? Is that enough to make the communication secured?

Authentication & Authorization: I create two user roles on the database, where one is the total admin and the other with basic rights which might prevent me from doing something stupid one day. For example deleting the wrong collection so and so.

  Second Question: What would be the best way to always make sure the
  mongo server is always responsive or running? (for example if the
  server was rebooted, I need to make sure that the mongo is still
  running) or (to avoid mongodb.lock file) because I had that issue before.

  Third Question: Since I am not doing replica set, what would be the
  best way to create a backup?

If you have any order advice, I am listening and thank you for your time.


texts: I'd like to set up SQL Audit on our SQL 2014 database and then setup a process where we read the contents of the audit file, on a periodic basis, using fn_get_audit_file() but then immediately truncate the audit file to prevent the files from accumulating and growing. I cannot find a way to truncate the audit file. Is it possible to do this?


texts: Can I change MSSQL interpretation of date order system wide?

texts:
I use Laravel with PHP from https://laravel.com/
MSSQL is a driver and works perfectly - with th exception of dates.
For some reason the project has decided that the follwoing is a universal date format

Y-m-d H:i:s
2021-01-21 12:00:00

However this does not seem to be universal as MSSQL interprets this as a US based date eg. The date above is invalid as 21 is considered the month not the day.
I will try to get them to allow date to be set to a custom format but this may take a while.
My MSSQL user is set to Language:British English which solves most of my issues, but not all.
Is it possible to in some way tell MSSQL to interpret a date like this

2021-01-22 12:00:00

as

YYYY-MM-DD HH:II:SS

instead of

YYYY-DD-MM HH:II:SS

which is what it is doing?
Can this be done on a system wide basis?


texts: Problems of having DR as Synchronous Commit

texts: In our always on set up we have One Primary and  One Secondary as Synchronous Commit and the DR as asynchronous commit. If i want to change the DR to Synchronous Commit will there be any performance issues ?


texts: SQL Server Transactional Replication With subscriber trigger returning a value

texts:
I have transaction replication setup on on SQL server 2008 R2. The subscriber has a after trigger on the published article that inserts into another table on the subscriber's database and returns the new value created in the trigger (the column is a persisted computed column). However, it seems we are not getting the new value back from the subscriber's trigger on the publisher after the insert occurs.

Is SQL server able to return the value from the trigger or because replication is transnational, which is just one way, it cannot?

UPDATE (2018-02-09 many many years later)

So hopefully I can better explain this than my past self:

A publisher, on a different SQL Server than the subscriber, publishes a table called dbo.Inventory. The subscriber subscribes to this publication and now has dbo.Inventory on its server. We add an AFTER INSERT trigger on the published table, dbo.Inventory, on the subscriber's SQL Server:

CREATE TRIGGER [dbo].[aiInsertNewPk]
  ON  [dbo].[Inventory] -- remember this trigger is on the subscriber
  AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @Serial TABLE (ColumnA CHAR(10));

INSERT INTO [Subscriber].[DifferentTable] ( <...columns> )
OUTPUT inserted.[Foobar] INTO @Serial(ColumnA)
SELECT <...columns>
FROM inserted
WHERE <logic>

-- output this value so c# application can grab it
SELECT ColumnA FROM @Serial

END

Our problem some years ago, was that trigger was not outputting the values from ColumnA (the last line of the trigger), so our C# application was always getting a null value back.

Not sure if that helps and honestly I have not revisited this issue to see if the problem was something else entirely. I just thought I would add this update since you were kind enough to comment many years later.


texts: (mysql innodb) Is single update statement with "where" transaction safe?

texts:
I understand that a single update statement is atomic, the row will be locked during update process. But when there is a "where" cause, is it still atomic?

For example:

table: ABC
-------------
|colA | colB|
-------------
|123  | 0   |

--update 1
update ABC set colA=456, colB=1 where colB=0;

--update 2
update ABC set colA=556, colB=2 where colB=0;

Due to the where cause, the update will first check colB value and find that row in the table, then lock the row for update, right?
If update 1 and update 2 is executed at the same time, is there a chance that both get successfully executed and one of them overwrite the value of another?

Is it better to write as below?

START TRANSACTION;
  update ABC set colA=456, colB=1 where colB=0;
COMMIT;


texts: Can I use replication/mirroring to have a "Current" database and a "Full History" copy?

texts:
I have a database that is used for current operations.  The amount of data is starting to get a bit to much for the applications using it.

Most of the data is older and is very rarely needed.

I am wondering if there is a way to configure SQL Server to keep a mirror of the original database, but allow me to occasionally purge out old data (and not have it purge from the copy).

NOTE: My database is currently SQL Server 2008 R2, but we will be upgrading to SQL Server 2012 soon.


texts: Can I perform a MAX over a pair of columns?

texts:
I would like to execute the following:

SELECT person, MAX( (date, priority) ) FROM table GROUP BY person;

It would return a person, date and priority row for each distinct value in the person column.  The date and priority are chosen such that the date value is maximised, and the maximum priority that occurs on that date is chosen.

As an example, running the query on this table

person |     date     | priority
---------------------------------
   1   | '2014-01-01' |    10
   1   | '2014-01-02' |     2
   1   | '2014-01-02' |     3

should result in

person |     date     | priority
---------------------------------
   1   | '2014-01-02' |     3

Postgres complains about this particular attempt:

ERROR:  function max(record) does not exist
HINT:  No function matches the given name and argument types.
You might need to add explicit type casts.

Is there a way that I can achieve this?  Anything that works with Postgres will be acceptable but if a solution is more standard then so much the better.


texts: How to traverse a tree in SQL? Sybase edition

texts:
Possible Duplicate:
  Traversing tree-like data in a relational database using SQL

Whats the best way to traverse a tree in sybase?

Basically, what I want is to gather all the decedents of a certain node.
(aka the decedents of the root node is every node in the tree)

I've thought of two things I can do.

Use recursion on the java end and query multiple times. (obviously less than optimal)
Transform the naturally recursive problem into an iterative problem and use a while loop and one or two temp tables. This wouldn't actually be that bad.

Is there a better way?


texts: How do you turn off the croak sound for Toad?

texts: How do you turn off this annoying feature? I don't want my DB manager to croak at me as loud as it can.


texts:
Hi i need to mix (more than join) three tables, but the id could be present or not on each table (Please see the example below).

I have spend a few days trying join and union but all time some data is missing!!! can someone give me a fresh idea?

Thanks


texts: Performance difference between single large Application instance vs multiple small Application Instance in Netweaver

texts: Can anyone please comment on the performance impact of having a single large application server instance vs multiple small application server instance in SAP Netweaver on a single machine.


texts: Index conditional aggregate query on PostgreSQL

texts:
How does one optimize a conditional aggregate query on PostgreSQL?

A example of such query would be:

SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL';

Indexing (PlayerId, status) would help the filter, any tricks or ideas to optimize the aggregation post filtration?

What would be ideal would be no external caching mechanism involved, and a save mechanism to keep a running SUM every time "games" table is updated – similar to how a cache works but a more reliable one.


texts: Is there a way to get an event when there's a change in a table?

texts:
Using Azure Sql Server, and in C# if that's relevant, is there a way to get an event when data in specific table changes?

In one table we need an event if any column changes in a row.

In a 2nd table we need an event only when either of 2 columns change (although any change would be ok).

And we need to know which row.

Is it possible to do this? If so, how?

Update: We need a call into our C# code for this event. A database trigger where the database can do something doesn't help, our program needs to take action on a change.

thanks - dave


texts:
Client's SQL Server farm DBs are backed up by third-party tool that does not use VSS. About half of the instances show backups being made by some tool that is using VSS. I can disable SQL Server VSS Writer to stop these errant backups, but how do I track down what is running the VSS backups?

keywords: Volume Shadow Copy Service, volume-shadow-service [I don't have rep to create tags]


texts:
I have a table of data invoices

invoice | vendor | date
0       | 0      | 08/01/2018
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
3       | 2      | 08/10/2018
4       | 2      | 08/10/2018
5       | 2      | 08/09/2018

I am trying to select the latest invoice number from each vendor, when there are more than one invoice for the same day I need the largest invoice number.

This is what I'm trying, but it gives me SQL Error: ORA-00979: not a GROUP BY expression sql developer references the beginning of first_value with that error.

SELECT max(date), first_value(invoice) OVER (order by date, invoice)
FROM invoices
GROUP BY vendor

I am using max(date) instead of max(invoice) because the largest invoice isn't always the latest date.

EDIT: this is what I'm trying to select

invoice | vendor | date
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
4       | 2      | 08/10/2018

EDIT2: added another row to demonstrate that the largest invoice number isn't always the latest date.


texts: Speedup restore of 160 GB SQL in 24 core + Optane NVME system

texts:
i set up a new sql-server, ubuntu 18.4 (MYSQL 5.7), 24 core (48 threads), 256G RAM, System is SATA Raid1 XFS SSD /var/lib/ is mounted on Optane SSD XFS.

In restore i can see that the server has unter 1.0 load so it is quit sleeping. I dont know what i can improve to speedup the restore.

Dump size is 30 GB gzip about 160gb 300 mio entries, attached my mysql.cnf
It is a stand alone server just for mysql.

The old server ist mysql 5.6, i dont know if it is able to copy the ibdata files?

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
explicit_defaults_for_timestamp = 1
#
key_buffer_size     = 6M
#max_allowed_packet = 16M
#thread_stack       = 192K
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 10000
table_open_cache       = 15000
open_files_limit= 17000
innodb_doublewrite = 0
log_error_verbosity = 1
innodb_buffer_pool_size = 220G
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 48
innodb_lock_wait_timeout=120
innodb_io_capacity = 400000
max_heap_table_size = 5G
max_allowed_packet=1G
sort_buffer_size=9M
join_buffer_size=9M
# * Query Cache Configuration
#
query_cache_limit   = 16M
query_cache_size        = 512M
query_cache_type    = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


texts: How to prevent read replica restarts during high replication lag

texts:
We're running an Aurora PostgreSQL cluster, with a single read replica along with the master node.

Periodically, there is very heavy write load which causes high replication lag.  This can cause the read replica to restart which is undesirable for us in a high availability environment. When this happens, clients that are connected to the cluster via the read-only endpoint get this JDBC error: org.postgresql.util.PSQLException: FATAL: the database system is starting up. Additionally, the AWS console shows these peppered throughout the logs:

  Read replica has fallen behind the master too much. Restarting postgres.

followed by

  DB instance restarted

We can tolerate the read replica being behind by several minutes, but we can't tolerate the read replica restarting to catch up.

Is there a way to prevent the read replica from restarting during these periods?

Alternatively, are there any recommended tweaks for reducing replication lag during periods of heavy write load?


texts: How to IFNULL or COALESCE rewrite in a SQL query?

texts:
I there a way to rewrite a SQL query, so I can avoid IFNULL or COALESCE, because the HXTT DBF JDBC driver (supports SQL92) throws the following error.:

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.

The following query causes the above error.:

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
    FROM tckopf AS shipManagementInvoice
    WHERE shipManagementInvoice.referenzid = 1
        AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
    FROM tckopf AS shipManagementCreditNote
    WHERE shipManagementCreditNote.referenzid = 1
        AND shipManagementCreditNote.btyp = 6 )
 AS shipManagementResult

If I remove IFNULL or COALESCE, there is no error.


texts:
My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the database, this makes for a very slow delete process when purging old user records.

Our delete query first updates all of the Updated/Added references to the current user and then deletes the user record. The execution plan shows table scans for each related table due to the foreign key. See this post as an example of what we are facing.

The pointers back to added and updated are not really useful other than for analysis during application problems. They are rarely used, and aren't a primary source of good information when they are. I'm wondering if I should remove the foreign key constraints all together or if I should possibly move the auditing of updates to a separate table that keeps a record of table name, column, value, and user id, or if there is some other generally accepted approach that the DBA community gravitates toward for this sort of situation.


texts: I am getting this error : "Error 102 : non-boolean type specified in a context where a condition is expected" for this request :

texts:
private void button2_Click(object sender, EventArgs e)
{
    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "delete from MYDB where First name='" + first.Text + "'";
    cmd.ExecuteNonQuery();
    con.Close();
    disp_data();
    MessageBox.Show("Contact deleted successfully");
}


texts:
I am trying to run the below statement, but I keep getting an error of

  Msg 102, Level 15, State 1, Line 23
  Incorrect syntax near '+'.

What do I change so this becomes valid syntax?

    Declare @salesman varchar(max) = 'Rich'

Select * FROM OPENQUERY(XXX.XXX.XXX.X,'Select
        cm.salesman salesmanName
        ,iv.saledate InvoiceDate
        ,CAST(COALESCE(jc.salecost,0) AS DOUBLE PRECISION) As totalsalecost
        ,jc.saleid saleid
FROM jobcost jc
        INNER JOIN arc ac
        ON jc.jcid = ac.jcid
        LEFT OUTER JOIN rama wp
        ON wp.jwid = ac.jwid
        Left join ira iv
        ON jc.saleid = iv.saleid
        LEFT JOIN sales jb
        ON jc.saleid = jb.saleid
        INNER JOIN salesman cm
        ON cm.arid = jb.arid
        WHERE wp.jwid IN (1,3,4,5,6) AND (ac.includeascost)
        AND jc.saleid IN (Select saleid from ira)
               AND cm.salesman IN ('+@salesman+')
               AND iv.saledate BETWEEN ''01/01/2016'' AND ''12/31/2016''')


texts: How to promote a PostgreSQL standby server by repmgr automatically with pgbouncer and barman?

texts:
Now using these servers:

Node1: pgbouncer1
Node2: pgbouncer2
Node3: repmgr primary
Node4: repmgr standby
Node5: repgmr standby
Node6: repmgr standby
Node7: barman primary
Node8: barman passive

pgbouncer for connection pool, repmgr for repliation and automatic failover, barman for backup and recovery.

Now want to add shell script to all the repmgr servers to automatic change a new primary if current(Node3) down. But maybe it's random select a new one(One of Node4, Node5, Node6).

If I provide promote script in all the repmgr servers, update self host IP and copy it to pgbouncer servers(Node1, Node2) and barman server(Node7). Which script will be ran ?

The repmgr.conf like:

# ...

service_promote_command = '/usr/local/bin/promote.sh'
promote_check_timeout = 15

promote_command='/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

promote.sh like:

#!/usr/bin/env bash
set -e
set -u

PGBOUNCER_DATABASE_INI_NEW="/tmp/pgbouncer.database.ini"
PGBOUNCER_HOSTS="Node1_IP Node2_IP"
DATABASES="db1 db2"

# Pause pgbouncer
for h in ${PGBOUNCER_HOSTS}
do
  for d in ${DATABASES}
  do
    ┆ psql -U postgres -h ${h} -p 5432 pgbouncer -tc "pause ${d}"
  done
done

# Promote server
/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file # Here possible?

# Generate new config file for pgbouncer
echo -e "[databases]\n" > ${PGBOUNCER_DATABASE_INI_NEW}
for d in ${DATABASES}
do
  echo -e "${d}= host=$(hostname -f)\n" >> ${PGBOUNCER_DATABASE_INI_NEW}
done

# Copy new config file, reload and resume pgbouncer
for h in ${PGBOUNCER_HOSTS}
do
  for d in ${DATABASES}
  do
    rsync -a ${PGBOUNCER_DATABASE_INI_NEW} ${h}:/etc/pgbouncer/pgbouncer.database.ini
    psql -U postgres -h ${h} -p 5432 pgbouncer -tc "reload"
    psql -U postgres -h ${h} -p 5432 pgbouncer -tc "resume ${d}"
  done
done

rm ${PGBOUNCER_DATABASE_INI_NEW}

# Copy new config file, reload and resume barman
# TODO

If deploy it as all the repmgr servers, can they run together, or select only one promoted primary server if current primary down?


texts:
I'm having a weird problem with mysqldump. I have a docker instance of mysql (5.7) to which I can connect just fine (to any database). I can also use mysqldump to dump the databases except one - after executing mysqldump on that database it simply hangs on "Connecting to " and does nothing.

I use simple:

mysqldump --routines=true -v -h <host> -u root -p<pass> <database>

I also tried adding: --single-transaction or -l without much success.

I can connect to this database just fine (if I switch mysqldump to mysql).

I'm not really sure how to debug this issue further as there are no errors nor log entries.


texts: Help to find bottleneck on slow view queries

texts:
I am trying to optimize two views but i don't know enough about SQL to make them faster.

The first one is this one : https://www.brentozar.com/pastetheplan/?id=BJVtbZJQM

and the second one that uses the first one : https://www.brentozar.com/pastetheplan/?id=SyoZX-1XG

Could it be possible to know where are the bottlenecks from the plan ? Thank you!


texts:
I create a user with CREATE LOGIN...

sp_grantdbaccess does not seem to enable the user

How can I enable a user using a script?

UPDATE: GRANT CONNECT does not seem to work, maybe I wasn't clear enough,
This is a SQL user (not Windows user) on SQL Server 2014, I'm looking at the main "Security-->Logins" tab that is common to all databases...


texts:
We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it smaller and lighter it shouldn't slow down everything.

CREATE TABLE [dbo].[TransactionEntry](
    [TransactionEntryID] [uniqueidentifier] NOT NULL,
    [TransactionID] [uniqueidentifier] NULL,
    [ItemStoreID] [uniqueidentifier] NULL,
    [Sort] [int] NULL,
    [TransactionEntryType] [int] NOT NULL,
    [Taxable] [bit] NULL,
    [Qty] [decimal](19, 3) NULL,
    [UOMPrice] [money] NULL,
    [UOMType] [money] NULL,
    [UOMQty] [decimal](19, 3) NULL,
    [Total] [money] NULL,
    [RegUnitPrice] [money] NULL,
    [DiscountPerc] [decimal](19, 3) NULL,
    [DiscountAmount] [money] NULL,
    [SaleCode] [nvarchar](50) NULL,
    [PriceExplanation] [nvarchar](50) NULL,
    [ParentTransactionEntry] [uniqueidentifier] NULL,
    [AVGCost] [money] NULL,
    [Cost] [money] NULL,
    [ReturnReason] [int] NULL,
    [Note] [nvarchar](50) NULL,
    [DepartmentID] [uniqueidentifier] NULL,
    [DiscountOnTotal] [decimal](19, 3) NULL,
    [Status] [smallint] NULL,
    [DateCreated] [datetime] NULL,
    [UserCreated] [uniqueidentifier] NULL,
    [DateModified] [datetime] NULL,
    [UserModified] [uniqueidentifier] NULL,
    [TotalAfterDiscount] [decimal](18, 3) NULL,
    [TaxID] [uniqueidentifier] NULL,
    [TaxRate] [decimal](18, 4) NULL,
 CONSTRAINT [PK_TransactionEntry] PRIMARY KEY CLUSTERED
(
    [TransactionEntryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO


texts: Add cumulative sum to time-series query PostgreSQL 9.5

texts:
I wrote the query that gives me time-series over some date range and interval that shows revenue for each time interval:

SELECT
    interval_date,
    coalesce(campaign_revenue,0) AS campaign_revenue,
FROM
    -- generate_series helps fill the empty gaps in the following JOIN
    generate_series(
        $2::timestamp,
        $3::timestamp,
        $4) AS interval_date -- could be '1 day', '1 hour' or '1 minute'.
LEFT OUTER JOIN
    -- This SELECT gets all timeseries rows that have data
    (SELECT
        date_trunc($4, s.created) AS interval,
        SUM(s.revenue) campaign_revenue
    FROM
        sale_event AS s
    WHERE
        s.campaignid = $1 AND s.created BETWEEN $2 AND $3 AND s.event_type = 'session_closed'
    GROUP BY
        interval) results
ON
    (results.interval = interval_date);

The query takes every row of sale_event table, truncates the created date to some interval (aligns the created timestamp with the time-series wanted granularity), groups by this time interval and sums up the revenue columns on the rows where event_type is session_closed.

This works very well and gives me the revenue in the specified interval. The result may look like:

interval_date   |   campaign_revenue
------------------------------------
 2018-08-05     |   0.0
 2018-08-06     |   1.5
 2018-08-07     |   0.0
 2018-08-08     |   0.5
 2018-08-09     |   1.0

When the provided range is 2018-08-05 - 2018-08-09 and interval = '1 day'.

I want to add to the result the sum of revenue up to that date. So if before 2018-08-05 there a total revenue of 10.0, the result would be:

interval_date   |   campaign_revenue   |   total_campaign_revenue
-----------------------------------------------------------------
 2018-08-05     |   0.0                |   10.0
 2018-08-06     |   1.5                |   11.5
 2018-08-07     |   0.0                |   11.5
 2018-08-08     |   0.5                |   12.0
 2018-08-09     |   1.0                |   13.0


texts:
Is there a way in DB2 to replace the entire table with just selected rows from the same table ?

Something like REPLACE into tableName select * from tableName where col1='a';
(I can export the selected rows, delete the entire table and load/import again, but I want to avoid these steps and use a single query).

Original table

col1   col2
 a      0 <-- replace all rows and replace with just col1 = 'a'
 a      1 <-- col1='a'
 b      2
 c      3

Desired resultant table

col1  col2
 a      0
 a      1


texts:
I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL).

The problem is that I cannot decide between having a 2-column table (with key and value fields) that contains multiple rows, one for each option; or an n-column table with one row.

It seems like using the single row format is superior, because each field can have the proper data type (INT, VARCHAR, etc.) and would make fetching all configuration values easier (single-row), but adding a new option would mean a new field every time.

At the same time, though, it seems like having a key-value table would possibly be a performance issue since you'd have to fetch all rows to get the full configuration, as well as updating having to find and seek the proper row to update; you also don't get actual typing (and would have to rely on VARCHAR or TEXT only).

I'm not planning on having a lot of configuration options (maybe 20 at the most, more likely 5-10), so I'm not sure if key-value or multi-field is the way to go, here.


texts: Can I get results from a Postgres query that I got disconnected from?

texts: I started an hours-long Postgres query in my psql shell. Then, I got disconnected from the network and had to make a new psql shell. If I look in pg_stat_activity, I see that my query is still running. Can I connect to that query and get its output?


texts: How to copy all firewall rules and apply them on a new server?

texts:
I often install new SQL Server Servers. Be them production, test or development.

There is one thing I still have not automated and it is time consuming: the firewall settings.

I know how to Configure Windows Firewall to Work with SQL Server but I want to automate it.

I want to be able to copy all settings from a firewall in an already existing server my_good_server_already_running and apply all those settings to my_new_server.

How can I get this done?

well, how to Enable these Windows Firewall rules using powershell? gives me an idea.

From Use PowerShell to List Firewall Rules I got the following script that allows me to list all the firewall rules that I am interested in:

Get-NetFirewallRule | Where { $_.Enabled –eq ‘True’ –and $_.Direction –eq ‘Inbound’ }

Get-NetFirewallRule | Where { $_.Enabled –eq ‘True’ –and $_.Direction –eq ‘Outbound’ }

How do I get these rules and make them a script to apply on my_new_server?

I tried Managing the Windows Firewall with PowerShell but it doesn't say how to make the applying script out of the list either.


texts: ORA-13901 Object UNDOTBS01 was not found. ORA-06512: at "SYS.DBMS_SERVER_ALERT", line 7

texts:
Oracle version is : 11.2.0.1

I have created alerting on every error occurring in database, and in this alert log I've found the following errors

ORA-13901: Object UNDOTBS01 was not found.
ORA-06512: at "SYS.DBMS_SERVER_ALERT", line 7
ORA-06512: at line 1

Username: DBSNMP

dbms_server_alert.set_threshold(:metrics_id,
,:warning_operator, :warning_value,
:critical_operator, :critical_value,
1,
:consecutive_occurrences,
:instance_name,
:object_type, :object_name);
END;

This error is repeating every 30-60 minutes.

Yesterday, I've created undo tablespace called UNDOTBS01, tried to assign it to the parameter undo_tablespace.

alter system set undo_tablespace=UNDOTBS01

Got the following error:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30015: previously offlined undo tablespace 'UNDOTBS1' is still pending

After that I dropped the UNDOTBS01 tablespace.

drop tablespace UNDOTBS01;

It was dropped.

I don't know what is causing the error and how to solve?


texts: sp_BlitzIndex performance concerns in production

texts:
No real concern but I'm looking for some information of possible impact of running it on a busy server.  Should I Be concerned?

Would there be any loss of fidelity in the results by running it on a restored instance?

Thank you.


texts:
A Table (mytable) is having 2 columns

id_       | loid_
(integer) | (oid)

Values are

id_    | loid_
1      | 15001
2      | 15002

Value in loid_ column refers the oid from pg_largeobject. And if I am running vacuumlo it wont show these two ids as orphaned object. If I removed these two entries from mytable and running vacuumlo, now these two ids being considered by vacuumlo as orphaned objects.

If I change the loid_ columns data type as integer by using below query,

alter table mytable alter column loid_ type integer; --works fine here

Update:

I have done some testing, seems its considering as orphaned object by vacuumlo.

it will impact anything? can I execute alter command?


texts: Enforce Data integrity on an Email column in a database

texts:
I want to put a constraint on an email column like so: xxxx@xxxx.yyy
Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc


texts: Insert into a DB that was selected via other DB?

texts:
I have a table that stores what database should I use for a given device( specified by its IMEI), like this:

imeiDB(
IMEI VARCHAR(15),
db VARCHAR(100));

Let's say I have the database device1(IMEI:123456789123456) is using stored in imeiDB:

123456789123456 | device1db

Supposing I have not selected the database to store the data, how can I select it on the insert into query of my table device1db.table1?

table1(IMEI VARCHAR(15),data VARCHAR(10))

I tried this but it did not work:

INSERT INTO (SELECT db FROM imeiDB where IMEI=123456789123456).table1 (IMEI,data) VALUES (123456789123456,"somestuff")

Any suggestions?


texts: I Need to Shrink My Database - I just freed a lot of space

texts:
This question is asked in various forms here but the question boils down to:

I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.

How can I shrink my database? What files do I shrink?
What should my considerations be while doing this?
Should I do anything after?
What if it is a large database? Can I shrink it in smaller increments?


texts: How to create Always Encrypted Certificate for specific time period?

texts:
I have configured Always Encrypted on a custom database using SQL Server 2016. By default, it creates the certificate with a validity of one year.

Is there any workaround to create this certificate for two or three years at the very beginning stage?

I don't want to rotate the key every year in our production environment rather create the certificate at least for two or three years.

Experts advise please.


texts: NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard

texts:
My issue is similar to this one but and I have tried what has been suggested but I still get the error. I have set the target column from VARCHAR(5120) to TEXT (tried LONGTEXT too) but the issue still keeps appearing of those exact columns. I even went into the schema and checked and it says they are TEXT. Am I getting this issue because the source column is VARCHAR(5120)? If so, is there any way to solve this. I simply want to use this migrated database as a local development copy with proper data.

The error message is as following:

  ERROR: gruppergister.Endring:SQLExecDirect(SELECT [id], CAST([endretFra] as NVARCHAR(8000)) as [endretFra], CAST([endretTil] as NVARCHAR(8000)) as [endretTil], CAST([endringskilde] as NVARCHAR(255)) as [endringskilde], CAST([sistEndretBruker] as NVARCHAR(255)) as [sistEndretBruker], CAST([sistEndretFulltNavn] as NVARCHAR(255)) as [sistEndretFulltNavn], [sistEndretTidspunkt], CAST([felt] as NVARCHAR(255)) as [felt], CAST([handling] as NVARCHAR(255)) as [handling], [aliste_id], [utvalg_id] FROM [gruppergister].[dbo].[Endring]):
  42000:1131:[Microsoft][ODBC SQL Server Driver][SQL Server]
  The size (8000) given to the convert specification 'nvarchar' exceeds the maximum allowed for any data type (4000).

It occurs during the "Bulk Data Transfer"


texts: Large queries on MyISAM tables crashing MySQL

texts:
I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that the site has grown very large in size, I have started to see performance on my MySQL server slow down, and due to the intense queries that ResourceSpace runs, the site will start having issues loading pages due to very long running queries.  When I login to MySQL, I will see multiple of the same queries running for very long amounts of time (300+ seconds), with most having the state of 'Waiting on table level lock'. The queries will never finish, and I either have to kill them, kill MySQL, or MySQL itself will completely crash. Has anyone ever seen behavior like this with this particular software or any PHP website in general?

Also, I am considering converting the engine on the tables over to InnoDB. I was wondering if there is any considerable risk with altering the tables and setting the engine to InnoDB?


texts: How to optimize a SQL Server query to fetch the results faster

texts:
Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns:

ID int (identity column),
SETUP_ID int
CUSTOMER_ID int,
BARCODE_LABEL nvarchar(20),
REGULATORY_AGENCY_ID int,
PACKAGE_LEVEL_ID int,
BATCH_NUMBER nvarchar(20),
PRODUCT_CODE nvarchar(20) ,
SERIAL_LIFECYCLE_ID int,
PACK_LIFECYCLE_ID int,

There is no problem in importing and processing the data using ASP.NET program.

Now when I query the database view (BARCODE_MANAGEMENT_VIEW), using the following query , I get a time out on my ASP.NET program as the query execution takes more than 1 min and 40 sec.

SELECT * FROM dbo.BARCODE_MANAGEMENT_VIEW
        WHERE
             (CUSTOMER_ID=@CUSTOMER_ID OR @CUSTOMER_ID=-1) AND
             (REGULATORY_AGENCY_ID=@REGULATORY_AGENCY_ID OR @REGULATORY_AGENCY_ID=-1) AND
             (BATCH_NUMBER= @BATCH_NUMBER OR @BATCH_NUMBER='-1') AND
            (PACKAGE_LEVEL_ID=@PACKAGE_LEVEL_ID OR @PACKAGE_LEVEL_ID=-1) AND
             (PRODUCT_CODE=@PRODUCT_CODE OR @PRODUCT_CODE=-1) AND
            (SERIAL_LIFECYCLE_ID=@SERIAL_LIFECYCLE_ID OR @SERIAL_LIFECYCLE_ID=-1)  AND
            (PACK_LIFECYCLE_ID=@PACK_LIFECYCLE_ID OR @PACK_LIFECYCLE_ID=-1)
        ORDER BY ID ASC

I have created the following indexes as well on the table and not sure how I can do it on a SQL VIEW:

CREATE NONCLUSTERED INDEX [IX_RetrieveList] ON [dbo].[BARCODE_MANAGEMENT]
(
    [SETUP_ID] ASC,
    [CUSTOMER_ID] ASC,
    [REGULATORY_AGENCY_ID] ASC,
    [BATCH_NUMBER] ASC,
    [PACKAGE_LEVEL_ID] ASC,
    [PRODUCT_CODE] ASC,
    [SERIAL_LIFECYCLE_ID] ASC,
    [PACK_LIFECYCLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

What am I doing wrong. I am a newbie to SQL Server database coding and I would appreciate any suggestions on how I could add new indexes to query for faster retrieval.


texts: Best Way To Create Time Series from Postgres Table Update History?

texts:
I have a Postgres Table (500 rows, 10 columns) that gets updated every 6 hours by a Daemon (that performs some calculations) and executes a series of row-by-row upsert operations. How can I construct day-by-day time series of the state of the table? Is there some way to build this by walking through the update/operation history? Are there any frameworks/techniques out there to make this easy?

Table will grow to max of 5000 rows.


texts:
I want to get a data of previous year : 2015-12-01

My Query :

  SELECT

          catalog.code,
          Max(catalog.weight) as weight,
          MONTH(product.created_at) as Month,
          YEAR(product.created_at) as year

  FROM product

 LEFT JOIN catalog ON product.code = catalog.code

 WHERE MONTH(product.created_at) = MONTH(2016-01-01 - INTERVAL 1 MONTH) AND YEAR(product.created_at) = year(NOW())

 GROUP BY catalog.code,MONTH(product.created_at),YEAR(product.created_at)

Does anyone have an idea how i can solve this problem?

Thanks for any help.